Home > SQL Developer Tutorial: Cre... > Create a Table (BOOKS)
The BOOKS table contains a row for each book in the library. It includes columns of character and number types, a primary key, a unique constraint, and a check constraint. 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 books ( book_id VARCHAR2(20), title VARCHAR2(50) CONSTRAINT title_not_null NOT NULL, author_last_name VARCHAR2(30) CONSTRAINT last_name_not_null NOT NULL, author_first_name VARCHAR2(30), rating NUMBER, CONSTRAINT books_pk PRIMARY KEY (book_id), CONSTRAINT rating_1_to_10 CHECK (rating IS NULL OR (rating >= 1 and rating <= 10)), CONSTRAINT author_title_unique UNIQUE (author_last_name, title));
To create the BOOKS table, connect to the database as the user in the schema you want to use 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.)
For detailed information about the table dialog box and its tabs, see Create Table (quick creation) and Create/Edit Table (with advanced options).
Schema: Specify your current schema as the schema in which to create the table.
Name: BOOKS
Create the table columns using the following information. After creating each column except the last one (rating), click Add Column to add the next column. (If you accidentally click OK instead of Add Column, right-click the BOOKS table in the Connections navigator display, select Edit, and continue to add columns.)
Column Name | Type | Size | Other Information and Notes |
---|---|---|---|
book_id | VARCHAR2 | 20 | Primary Key (Automatically checks Not Null; an index is also created on the primary key column. This is the Dewey code or other book identifier.) |
title | VARCHAR2 | 50 | Not Null |
author_last_name | VARCHAR2 | 30 | Not Null |
author_first_name | VARCHAR2 | 30 | |
rating | NUMBER | (Librarian's personal rating of the book, from 1 (poor) to 10 (great)) |
After you have entered the last column (rating), check Advanced (next to Schema). This displays a pane for more table options. For this table, you will use the Unique Constraints and Check Constraints panes.
Unique Constraints pane
Click Add to add a unique constraint for the table, namely, that the combination of author_last_name and title must be unique within the table. (This is deliberately oversimplified, since most major libraries will have allow more than one copy of a book in their holdings. Also, the combination of last name and title is not always a "foolproof" check for uniqueness, but it is sufficient for this simple scenario.)
Name: author_title_unique
In Available Columns, double-click TITLE and then AUTHOR_LAST_NAME to move them to Selected Columns.
Check Constraints pane
Click Add to add a check constraint for the table, namely, that the rating column value is optional (it can be null), but if a value is specified, it must be a number from 1 through 10. You must enter the condition using SQL syntax that is valid in a CHECK clause (but do not include the CHECK keyword or enclosing parentheses for the entire CHECK clause text).
Name: rating_1_to_10
Condition: rating is null or (rating >= 1 and rating <= 10)
Click OK to finish creating the table.
Go to Create a Table (PATRONS) to create the next table.
Related Topics
SQL Developer Tutorial: Creating Objects for a Small Database