Home > SQL Developer Tutorial: Cre... > Create a Table (TRANSACTIONS)
The TRANSACTIONS table contains a row for each transaction involving a patron and a book (for example, someone checking a book out or returning a book). It includes two foreign key columns. You will use the Create Table dialog box to create the table declaratively; the table that you create will be essentially the same as if you had entered the following statement using the SQL Worksheet:
CREATE TABLE transactions ( transaction_id NUMBER, patron_id CONSTRAINT for_key_patron_id REFERENCES patrons(patron_id), book_id CONSTRAINT for_key_book_id REFERENCES books(book_id), transaction_date DATE CONSTRAINT tran_date_not_null NOT NULL, transaction_type NUMBER CONSTRAINT tran_type_not_null NOT NULL, CONSTRAINT transactions_pk PRIMARY KEY (transaction_id));
To create the TRANSACTIONS table, if you are not already connected, connect to the database as the user for the schema you are using for this tutorial. Right-click the Tables node in the schema hierarchy on the left side, select New Table, and enter the following information. (If a tab or field is not mentioned, do not enter anything for it. Be sure that the Advanced box is not checked when you start creating the table.)
Schema: Specify your current schema as the schema in which to create the table.
Name: TRANSACTIONS
Create the table columns using the following information. After creating each column except the last one (transaction_type), click Add Column to add the next column. (If you accidentally click OK instead of Add Column, right-click the TRANSACTIONS table in the Connections navigator display, select Edit, and continue to add columns.)
Column Name | Type | Size | Other Information and Notes |
---|---|---|---|
transaction_id | NUMBER | Primary Key. (Unique transaction ID number, with values to be created using a trigger and sequence that will be created automatically) | |
patron_id | NUMBER | (Foreign key; must match a patron_id value in the PATRONS table) | |
book_id | VARCHAR2 | 20 | (Foreign key; must match a book_id value in the BOOKS table) |
transaction_date | DATE | (Date and time of the transaction) | |
transaction_type | NUMBER | (Numeric code indicating the type of transaction, such as 1 for checking out a book) |
After you have entered the last column (transaction_type), check Advanced (next to Schema). This displays a pane for selecting more table options. For this table, you will use the Column Sequences and Foreign Keys panes.
Column Sequences pane
You have already specified TRANSACTION_ID as the primary key, and you will use this pane only to specify that the primary key column values are to be populated automatically. This convenient approach uses a trigger and a sequence (both created automatically by SQL Developer), and ensures that each transaction ID value is unique.
Column: TRANSACTION_ID
Sequence: New Sequence
Trigger: TRANSACTIONS_TRG (The default; a before-insert trigger with this name will be created automatically.)
Foreign Keys tab
1. Click Add to create the first of the two foreign keys for the TRANSACTIONS table.
Name: for_key_patron_id
Referenced Schema: Name of the schema containing the table with the primary key or unique constraint to which this foreign key refers. Use the schema you have been using for this tutorial.
Referenced Table: PATRONS
Referenced Constraint: PATRONS_PK (The name of the primary key constraint for the PATRONS table. Be sure that the Referenced Column on PATRONS displayed value is PATRON_ID.)
Associations: Local Column: PATRON_ID
Associations: Referenced Column on PATRONS: PATRON_ID
2. Click Add to create the second of the two foreign keys for the TRANSACTIONS table.
Name: for_key_book_id
Referenced Schema: Name of the schema containing the table with the primary key or unique constraint to which this foreign key refers. Use the schema you have been using for this tutorial.
Referenced Table: BOOKS
Referenced Constraint: BOOKS_PK (The name of the primary key constraint for the BOOKS table. Be sure that the Referenced Column on BOOKS displayed value is BOOK_ID.
Associations: Local Column: BOOK_ID
Associations: Referenced Column on BOOKS: BOOK_ID
3. Click OK to finish creating the table.
You have finished creating all the tables. To create a sequence for use in generating unique primary key values for the PATRONS table, go to Create a Sequence.
Related Topics
SQL Developer Tutorial: Creating Objects for a Small Database