A duplicate row is defined as a row whose primary key columns have the same values as those of another row.

Listing 4.15. Primary key is enforced.

SQL> insert into Department
  2  (Department_ID, Department_Name)
  3  values
  4  (`PSYCH', `PSYCHOLOGY');

1 row created.

SQL> insert into Department
  2  (Department_ID, Department_Name)
  3  values
  4  (`PSYCH', `PSYCHIATRY');
insert into Department
            *
ERROR at line 1:
ORA-00001: unique constraint (TYO.PK_DEPARTMENT) violated

You also have the option of defining the primary key after the table has been created.

Defining a Primary Key After Table Creation

You also have the option of defining a primary key constraint after a table has been created. Of course, the table may not have a primary key already defined. To define a primary key constraint for an existing table, use the ALTER TABLE statement, as shown in Listing 4.16.

Listing 4.16. Adding a primary key.

SQL> create table Department
  2  (Department_ID     Varchar2(20),
  3   Department_Name   Varchar2(25)
  4   constraint NN_Department_Name NOT NULL);

Table created.

SQL> alter table Department add
  2  constraint PK_Department primary key (Department_ID);
Table altered.

The primary key constraint is enforced whether the primary key was defined when the table was first created or added after the table already exists.

The Foreign Key and Referential Integrity

Primary and foreign keys work together to enforce referential integrity. A foreign key in a table is a column, or set of columns, whose values are restricted to those of the primary key in another table. You should define foreign keys whenever possible. For client/server applications, the first line of defense for referential integrity is the client application software. The last line of defense for referential integrity is the primary and foreign keys that have been defined for the database.

As with a primary key, a foreign key can be declared when a table is first created.

Declaring a Foreign Key During Table Creation

To illustrate the definition of a foreign key, let's look at the Department and Instructor tables in the Flugle College sample database. Each department has a unique ID that is kept in the column Department_ID. Every instructor must belong to a single department. In a real application, you probably would want to allow for the possibility that an instructor could be interdisciplinary by belonging to more than one department.

Oracle enforces referential integrity by disallowing any operations that would permit a foreign key to have a value that doesn't exist in the table containing the referenced primary key. For example, Listing 4.17 demonstrates that you cannot insert a record that describes an instructor who belongs to a Department ID equal to `PHYSICS' because that Department ID doesn't exist in the Department table.

Listing 4.17. Referential integrity is enforced.

SQL> insert into Instructor
  2  (Instructor_ID, Department_ID, Last_Name)
  3  values
  4  (`S1000', `PHYSICS', `MICHELSON');
insert into Instructor
*
ERROR at line 1:
ORA-02291: integrity constraint (TYO.FK_INSTRUCTOR_INSTRUCTOR_ID)
violated - parent key not found

As an alternative, you can opt not to specify the datatype for a column that is a foreign key. For example, Listing 4.18 furnishes an example in which no datatype is specified in the declaration of Department_ID in the Course table. Instead, Oracle looks up the datatype and width for the Department_ID column in the Department table and uses those definitions when creating the Course table.

Listing 4.18. Datatype not specified for foreign key column.

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

Table created.

SQL> describe Course
 Name                           Null?    Type
 ------------------------------ -------- ----
 COURSE_ID                      NOT NULL VARCHAR2(5)
 DEPARTMENT_ID                  NOT NULL VARCHAR2(20)
 TITLE                          NOT NULL VARCHAR2(60)
 DESCRIPTION                             VARCHAR2(2000)
 UNITS                                   NUMBER
 ADDITIONAL_FEES                         NUMBER

The advantage of not declaring a datatype for a foreign key column is that you are guaranteed that the foreign key column will have the same datatype definition as its primary key counterpart. The disadvantage is that you can't determine the foreign key column's datatype by examining the CREATE TABLE statement.

Declaring a Foreign Key After Table Creation

As an alternative to declaring a foreign key when you create a table, you have the option of declaring a foreign key on an existing table with the ALTER TABLE statement. Listing 4.19 provides an example of how a foreign key can be declared with the ALTER TABLE statement.

Listing 4.19. Adding a foreign key.

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

Table created.

SQL> alter table Course add constraint FK_Course_Department_ID
  2  foreign key (Department_ID) references Department (Department_ID);

Table altered.

Primary and Foreign Key Columns

When you define a foreign key, Oracle verifies the following:

• A primary key has been defined for the table referenced by the foreign key.
• The number of columns composing the foreign key matches the number of primary key columns.
• The datatype and width of each foreign key column matches the datatype and width of each primary key column.

Listing 4.20 illustrates what happens if the primary key column datatype doesn't match the column intended to be a foreign key. For example, the Employee table contains em-ployees that work for a company, and Employee_Dependent contains dependents of those employees. The primary key of the Employee table is Employee_ID. Of course, the Employee_ Dependent table also contains Employee_ID. Observe that the Employee table's definition for Employee_ID is NUMBER(4), but the Employee_Dependent table's definition for Employee_ID is VARCHAR2(4). As a result, Oracle does not allow the foreign key to be defined for Employee_ID in Employee_Dependent, which references Employee_ID in Employee.