6.2 How do I…Add a foreign key constraint? Problem

I have many applications updating the same sets of data. I want to ensure that the business rules governing the relationships between data in tables is enforced. It is important that no application can create data that violates the data relationships in our data model. Each application currently checks to ensure that new data and changes to existing data are valid. I want to enforce referential integrity at the database level using foreign keys. How do I add foreign key constraints?

Technique

Referential integrity is a feature of Oracle that ensures that all references to external objects within each database object are valid. Referential integrity is enforced by using a combination of primary keys and foreign keys. A primary key consists of one or more columns that uniquely identifies a row in a table. A primary key can be created in the CREATE TABLE statement or added later with an ALTER TABLE statement. The primary key ensures that each row in the table is unique. How-To 6.1 covered the creation of primary keys. A foreign key defines the columns in a table that must exist as a primary key of the same or another table. A foreign key can also reference a unique key of the same or another table. A foreign key references a referenced key (primary key or unique key) and not table columns directly. A foreign key constraint can be created in the CREATE TABLE statement or added later with an ALTER TABLE statement.

Steps

1. Run SQL*Plus and connect as the WAITE user account. Run the SQL in the CHP6_5.SQL file, shown in Figure 6.5, which contains the statements to create the two sample tables used in this How-To. The DEPT06 and EMP06 tables created in the script will be related by the common DEPT_NO column. The DEPT_NO column of the DEPT06 table is the primary key for the table and will be referenced by the same field in the EMP06 table. Thus, in achieving referential integrity, the primary key constraint of the DEPT06 table is referenced as a foreign key in the EMP06 table.

SQL> START CHP6_5

SQL> SET ECHO OFF

Table created.

Table created.

The CREATE TABLE keyword is used to create two new tables, DEPT06 and EMP06. The DEPT_NO in the EMP06 table is defined as NOT NULL, to ensure that there is no employee without a department. Observe the use of the CONSTRAINT clause to create a named primary key in the DEPT06 table and a named foreign key in the EMP06 table. The CONSTRAINT clause is optional in both tables. Finally, using the REFERENCE clause, this foreign key is bound to the primary key of the DEPT06 table. The DEPT_NO column of the DEPT06 on which the primary is defined is explicitly referred to as - REFERENCES DEPT06(DEPT_NO) - in the EMP06 table. The column name could have been omitted in the REFERENCES clause as - REFERENCES DEPT06 -, and Oracle will automatically bind the foreign key with the default primary key of the DEPT06 table.

2. Run the statements in the CHP6_6.SQL file. This file contains two ALTER TABLE statements: the first ALTER TABLE statement to drop the existing foreign key created in step 1, and a second one to create a foreign key constraint between the two tables. Any value for the DEPT_NO field in the EMP06 table must have a corresponding record in the DEPT06 table. Figure 6.6 shows the results of running the two ALTER TABLE statements in SQL*Plus.

The first command is an ALTER TABLE statement with the DROP CONSTRAINT keywords to drop the foreign key from the EMP06 table. This is necessary before we create the same foreign key for the EMP06 table using the ALTER TABLE syntax. The ADD CONSTRAINT clause contained in line 2 is used to add a named constraint to the table. The CONSTRAINT clause is optional and can be omitted if you have no need for the foreign key to have a specific name. Line 3 contains the FOREIGN KEY clause to create a foreign key on the DEPT_NO column of the EMP06 table. Line 4 makes this foreign key reference the default primary key of the DEPT06 table.

3. Run the CHP6_7.SQL file, which contains an INSERT statement to attempt to insert a record into the EMP06 table. Because there are no rows in DEPT06 table, the foreign key reference to the DEPT_NO in the DEPT06 will fail. Figure 6.7 shows the results of running the statement in SQL*Plus.

An ORA-02291 error is generated and the name of the constraint violated appears in the error message. For a statement inserting invalid data to succeed, the constraint must be disabled. How-To 6.4 explores the topic of disabling constraints. Also, if a record corresponding to the foreign key had existed in the DEPT06 table beforehand, then the statement will run successfully. This is demonstrated by running the CHP6_8.SQL to insert records in the proper order as shown in Figure 6.8.

A record is first inserted into the DEPT06 table. The value of the primary key of the DEPT06 record is then used as the foreign key while inserting a record in the EMP06 table. Applications must be designed to insert records in a parent table before the child table.

How It Works

Step 1 creates the tables used throughout this How-To. A primary key on the DEPT06 table is also created. A primary key must exist on the master table before creating a foreign key in the detail table. While creating the EMP06 table, both a primary and foreign key are created. The foreign key references the primary key of the DEPT06 table. In order to create a record in the EMP06 table, there must be a corresponding record in the DEPT06 table. In Step 3, inserting an invalid record into the EMP06 table is attempted initially. This is followed by presenting the proper method of inserting records in tables with a master-detail relationship.

While being referenced by one or more records in the EMP06 table, if you try to delete that record from the DEPT06 table before deleting the referencing EMP06 records, an ORA-02292 error will occur. The delete operation in a master table is only possible if there are no rows in the detail table that refer to the rows being deleted from the master table, or if the foreign key relationship is defined using the ON DELETE CASCADE option (refer to How-To 6.7).

A foreign key can reference the primary key of the same table. This can be used to enforce hierarchical relationships within a single table. For instance, each manager is also an employee, so the MGR_NO column can be made as a foreign key referencing the EMP_NO column of the EMP06 table. This is termed as a self-referential integrity constraint.

Comments

Referential integrity helps ensure validity of data. It is not necessary that columns of the parent (master) and child (detail) tables have the same names, but they must be of the exact same datatype. Composite foreign keys are limited to 16 columns just like composite primary keys. If the column list is not included in the REFERENCES clause when defining a foreign key, the primary key of the specified table will be referenced by default. As rows in the child table reference the key in the parent table, it is not possible to UPDATE the parent key or DELETE that parent table record, without first deleting the referencing record from the child table. This functionality is also known as UPDATE RESTRICT and DELETE RESTRICT, respectively. If an INSERT or UPDATE is issued on the child table, foreign keys must have a value of the referenced key already existing in the parent table, or NULL. Unlike a primary key, a NOT NULL constraint is not implicit with a foreign key and has to be explicitly defined with a foreign key, if required. A referenced key can be referred by any number of foreign keys.

The application must have knowledge of constraints, regardless of how integrity is enforced in the database. The application must submit transaction statements in the proper order, in accordance with the referential integrity constraints. The application must also handle exceptions resulting from integrity violations. Referential integrity implemented through applications instead of foreign keys results in better performance, keeping in mind that a foreign key constraint necessitates an additional read.

There must be privileges, too. The user account must either be the owner of the parent table, or have the REFERENCES privilege to the columns of the primary key of the parent table. And to create the foreign key constraints in the child table, the user account must have the ability to create tables (CREATE TABLE or CREATE ANY TABLE privilege); or the ability to alter the child table (ALTER TABLE or ALTER ANY TABLE privilege). These privileges have to be explicitly granted to the creator of the constraint and cannot be obtained via a role.

Note - Oracle does not automatically create non-unique indexes on foreign keys. It is a good idea to explicitly create non-unique indexes on all foreign keys for improved performance and concurrency control.