Listing 4.20. Foreign key cannot be created because of column datatype mismatch.

SQL> describe Employee
 Name                           Null?    Type
 ------------------------------ -------- ----
 EMPLOYEE_ID                    NOT NULL NUMBER(4)
 LAST_NAME                      NOT NULL VARCHAR2(30)
 FIRST_NAME                     NOT NULL VARCHAR2(20)
 MIDDLE_INITIAL                          CHAR(1)
 HIRE_DATE                      NOT NULL DATE
 TERMINATION_DATE                        DATE
 DATE_OF_BIRTH                           DATE
 MONTHLY_SALARY                          NUMBER(5)
 MANAGER                        NOT NULL CHAR(1)
 USERNAME                                VARCHAR2(31)

SQL> describe Employee_Dependent
 Name                           Null?    Type
 ------------------------------ -------- ----
 EMPLOYEE_ID                    NOT NULL VARCHAR2(4)
 LAST_NAME                               VARCHAR2(30)
 FIRST_NAME                              VARCHAR2(20)
 MIDDLE_INITIAL                          CHAR(1)
 RELATIONSHIP                            VARCHAR2(30)

SQL> alter table Employee_Dependent add Constraint Employee_Dependent_FK1
  2              Foreign Key (Employee_ID) references Employee;
            Foreign Key (Employee_ID) references Employee
                         *
ERROR at line 2:
ORA-02256: number, type and size of referencing columns must match
referenced columns

Disabling and Enabling Key Constraints

As I mentioned previously, primary and foreign key constraints enforce two crucial aspects of the relational model: data and referential integrity. You may want to disable these constraints for at least two tasks: designing the database and migrating the organization's legacy data.

Dropping a Primary Key

During the database design process, you may need to drop a table's primary key.

To do so, use the ALTER TABLE statement with this syntax:

ALTER TABLE table-name DROP PRIMARY KEY;

The variable is defined as follows:

table-name is the table associated with the primary key.

For the sake of illustration, suppose you create a table for the storage of accounts payable data and define the primary key to be the Vendor's invoice number, as shown in Listing 4.21. After inspecting some data and pondering this definition, you realize that two different vendors could easily supply the same invoice number. Therefore, you drop the primary key for the table.

Listing 4.21. Dropping a primary key.

SQL> create table AP_Header (
  2  Bill_Number            NUMBER(4) NOT NULL,
  3  Vendor_Invoice_Number  VARCHAR2(10),
  4  Vendor_ID              VARCHAR2(6)  NOT NULL,
  5  Date_Received          DATE         NOT NULL,
  6  Bill_Status            VARCHAR2(5),
  7  primary key (Vendor_Invoice_Number));
Table created.

SQL> alter table AP_Header drop primary key;
Table altered.

What if you've already declared a foreign key in the AP_Detail table that references the primary key of AP_Header? If that is the case, Oracle does not allow you to drop AP_Header's primary key. Listing 4.22 illustrates this.

Listing 4.22. Unable to drop a primary key.

SQL> create table AP_Detail (
  2  Bill_Number            NUMBER(4) NOT NULL,
  3  Vendor_Invoice_Number  VARCHAR2(10) NOT NULL,
  4  Item_Number            NUMBER(3) NOT NULL,
  5  Billed_Amount          NUMBER(8,2) NOT NULL,
  6  Approved_Amount        NUMBER(8,2),
  7  Paid_Amount            NUMBER(8,2),
  8  Constraint AP_Detail_FK Foreign Key (Vendor_Invoice_Number)
  9             References AP_Header);
Table created.

SQL> alter table AP_Header drop primary key;
alter table AP_Header drop primary key
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys

There is an option to the DROP PRIMARY KEY clause; you can use the keyword CASCADE. Use this feature with caution! CASCADE drops the primary key as well as any foreign keys that reference it. Listing 4.23 shows how the primary key is successfully dropped with the CASCADE clause.

Listing 4.23. Dropping a primary key with the CASCADE clause.

SQL> alter table AP_Header drop primary key cascade;
Table altered.

Dropping a Foreign Key

During the database design process, you may find that you've mistakenly defined a column as a foreign key. Dropping a foreign key is a bit different than dropping a primary key.

Because a table can have more than one foreign key, the ALTER TABLE statement requires that you supply the constraint name associated with the foreign key, using this syntax:

ALTER TABLE table-name DROP CONSTRAINT
constraint-name;

The variables are defined as follows:

table-name is the table associated with the primary key.

constraint-name is the constraint associated with the foreign key.

For instance, suppose that the AP_Header table has an additional column named Vendor_Status, which, coincidentally, has the same datatype and width as Vendor_ID. As Listing 4.24 shows, you mistakenly create a foreign key for Vendor_Status that references the primary key of the Vendor table. After you try to insert a value into the Vendor_Status column, you quickly realize your error and drop the foreign key assigned to the column.

Listing 4.24. Dropping a foreign key.

SQL> alter table AP_Header add constraint AP_Header_Vendor_Status_FK
  2                            foreign key (Vendor_Status) references Vendor;
Table altered.

SQL> alter table AP_Header drop constraint AP_Header_Vendor_Status_FK;
Table altered.

Declaring Unique Constraints

In addition to primary and foreign keys, Oracle enables you to indicate that a column must have unique values. A unique constraint is not a substitute for a primary key constraint. As an example, the Patient table contains a list of patients in a hospital. Each patient is assigned a Patient ID, which is used as the primary key. However, each patient also has a Social Security number that is unique. Listing 4.25 provides an example of how a unique Social Security number is enforced with a unique constraint.

Listing 4.25. Creating a unique constraint.

SQL> create table Patient (
  2  Patient_ID           varchar2(6) primary key,
  3  Last_Name            varchar2(30) not null,
  4  First_Name           varchar2(20) not null,
  5  Middle_Name          varchar2(20),
  6  Social_Security_Number varchar2(9) unique,
  7  Insurance_Carrier_Code varchar2(4));
Table created.

SQL> insert into Patient
  2  (Patient_ID, Last_Name, First_Name)
  3  values
  4  (`A901', `NORTON', `ED');
1 row created.

SQL> insert into Patient
  2  (Patient_ID, Last_Name, First_Name, Social_Security_Number)
  3  values
  4  (`A902', `KRAMDEN', `RALPH', `123456789');
1 row created.

SQL> insert into Patient
  2  (Patient_ID, Last_Name, First_Name, Social_Security_Number)
  3  values
  4  (`A903', `NORTON', `TRIXIE', `123456789');
insert into Patient
            *
ERROR at line 1:
ORA-00001: unique constraint (TYO.UQ_PATIENT_PATIENT_ID) violated

Differences Between Primary Key and Unique Constraints

Two differences between primary key and unique constraints are worth noting. First, a table can have only one primary key, but it can have many unique constraints. Second, when a primary key is defined, the columns that compose the primary key are automatically mandatory. When a unique constraint is declared, the columns that compose the unique constraint are not automatically defined to be mandatory; you must also specify that the column is NOT NULL.

Table Indexes

Any discussion of primary and foreign key constraints must also examine indexes—the topics are closely related. This section discusses what table indexes are and how they are used by both the application developer and Oracle.