As I mentioned, Oracle's implementation of SQL is not case sensitive (which isn't true of all vendors) as long as table and column names are not enclosed in double quotes. Listing 4.3 provides an example of the Student table in which the table and column names are enclosed in double quotes. However, Oracle will not recognize the table or column names unless they are always placed in double quotes.

Listing 4.3. Enclosing table and column names in double quotes.

SQL> create table "student"
  2  ("student_id"   Varchar2(20)  NOT NULL,
  3   "last_name"    Varchar2(25)  NOT NULL,
  4   "first_name"   Varchar2(25)  NOT NULL,
  5   "mi"           Varchar2(1),
  6   "year"         Varchar2(25),
  7   "street_address" Varchar2(25),
  8   "city"           Varchar2(25),
  9   "state"          Varchar2(2),
 10   "zipcode"        Varchar2(10),
 11   "telephone"      Varchar2(10),
 12   "fax"            Varchar2(10),
 13   "email"          Varchar2(100));

Table created.

SQL> describe student

Object does not exist.

SQL> describe "student"

Name                            Null?    Type
 ------------------------------ -------- ----
 student_id                     NOT NULL VARCHAR2(20)
 last_name                      NOT NULL VARCHAR2(25)
 first_name                     NOT NULL VARCHAR2(25)
 mi                                      VARCHAR2(1)
 year                                    VARCHAR2(25)
 street_address                          VARCHAR2(25)
 city                                    VARCHAR2(25)
 state                                   VARCHAR2(2)
 zipcode                                 VARCHAR2(10)
 telephone                               VARCHAR2(10)
 fax                                     VARCHAR2(10)
 email                                   VARCHAR2(100)

Identifying the Primary Key

A table's primary key is the set of columns that uniquely identifies each row in the table.

Here's another look at the CREATE TABLE syntax:

CREATE TABLE table_name (
column_name1 datatype [NOT NULL],
...
column_nameN datatype [NOT NULL],
[Constraint constraint_name]
[Primary key (column_nameA, column_nameB, ... column_nameX)]);

The variables are defined as follows:

table_name is the name for the table.
column_name1 through column_nameN are valid column names.
datatype is a valid Oracle datatype specification.
constraint_name is an optional name that identifies the primary key constraint.
column_nameA through column_nameX are the table's columns that compose the pri-mary key.

Although the primary key clause of the CREATE TABLE statement is an essential concept in relational database theory, in practice its use is optional. Using the Department table again, Listing 4.4 shows how the primary key is declared during table creation.

Listing 4.4. Declaring the primary key during table creation.

SQL> create table Department
  2  (Department_ID Varchar2(20),
  3   Department_Name   Varchar2(25),
  4  Constraint PK_Department
  5  Primary Key (Department_ID));

Table created.

Notice that I do not use the NOT NULL constraint to specify that the Department ID is a mandatory column. By default, Oracle forces all columns that comprise the primary key to be mandatory.

Primary keys are subject to several restrictions. First, a column that is part of the primary key cannot be null. Second, a column that is defined as LONG or LONG RAW cannot be part of the primary key. Third, the maximum number of columns in the primary key is 16. You can circumvent this last restriction through the use of a surrogate key—an artificial value that can be guaranteed to uniquely identify all rows in a table.

Identifying Foreign Keys

Now that you know how to specify the primary key in the CREATE TABLE statement, you are ready to declare your foreign keys.

The following CREATE TABLE syntax includes primary and foreign key declarations:

CREATE TABLE table_name (
column_specification1,
...
column_specificationN,
[Constraint constraint_name Foreign key (column_nameF1,...column_nameFN)
references referenced_table (column_nameP1,...column_namePN),]
[Constraint constraint_name Primary key

(column_nameA, column_nameB, ... column_nameX)]);

The variables are defined as follows:

table_name is the name for the table.
column_specification1 through column_specificationN are valid column specifications (described below in detail).
constraint_name is the constraint name that you want to assign to the foreign key.
referenced_table is the name of the table referenced by the foreign key declaration.
column_nameF1 through column_nameFN are the columns that compose the foreign key.
column_nameP1 through column_namePN are the columns that compose the primary key in referenced_table.
column_nameA through column_nameX are the table's columns that compose the pri-mary key.

The syntax for a column_specification follows.

column_name datatype [DEFAULT default_value]
[CONSTRAINT constraint_name] [NULL]|[NOT NULL]|[UNIQUE]|CHECK (condition)

The variables are defined as follows:

column_name is a valid Oracle column name.
datatype is a valid Oracle datatype specification.
default_value is a legal default value assigned to the column on an insert.
constraint_name is a legal constraint name to be assigned to the constraint.
condition is a valid Oracle Boolean condition that must be true for a value to be assigned to a column.

A constraint is a mechanism which ensures that the values of a column or a set of columns satisfy a declared condition.

For the Course table, you need to declare a primary and foreign key as demonstrated in Listing 4.5.

Listing 4.5. Declaring primary and foreign keys during table creation.

SQL> create table Course
  2  (Course_ID       Varchar2(5),
  3   Department_ID   Varchar2(20) NOT NULL,
  4   Title           Varchar2(60) NOT NULL,
  5   Description     Varchar2(2000),
  6   Units           Number,
  7   Additional_Fees Number,
  8   Constraint PK_Course
  9   Primary key (Course_ID, Department_ID),
 10   Constraint FK_Course_Department_ID
 11   Foreign key (Department_ID) references Department (Department_ID));

Table created.

The columns in the referenced table must actually compose the primary or unique key of the referenced table. If they don't, Oracle will not create the foreign key. In the following example, let's assume that you have not yet created the foreign key for the Course table. First, let's drop the primary key from the Department table. When you try to create the Course table, Oracle will determine that Department_ID is not defined as the primary key for the Department table; the CREATE TABLE statement will fail, as illustrated in Listing 4.6.