Foreign Key Constraints

A foreign key constraint will validate the values of an INSERT or UPDATE against the values in another column. Normally the column is in another table, but it could be another column in the same table. This is a parent/child relationship. The parent is the column (or columns) referenced in the foreign key. The child is the column (or columns) that contain the foreign key constraint.

When data is inserted or updated in the child column, the foreign key will verify that the value exist in the parent column. If the value does not exist, the foreign key constraint will not allow the value in the child column. Let’s look at the EMP table.

SQL> desc emp
 Name 					   Null?     Type
 ----------------------------------------- -------- -------------- 
EMP_KEY 					     VARCHAR2(9)
 JOB_KEY 					     NUMBER(5)
 PUB_KEY    					     VARCHAR2(4)
 EMP_FIRST_NAME 				     VARCHAR2(20)
 EMP_MI 					     VARCHAR2(1)
 EMP_LAST_NAME 					     VARCHAR2(30)
 EMP_DATE_OF_HIRE      				     DATE
 EMP_SALARY 					     NUMBER(6)
 MANAGER 					     VARCHAR2(9)

Each employee has a manager, except the manager. You can find the manager by looking at the manager column for each employee. The manager column contains the employee key of the manager for that employee. One of our business rules is that all managers must be an employee. We can enforce this rule with a foreign key constraint.

The parent column is emp_key. The child column is manager. When I insert or update a value in the manager column (change the employee’s manager), the foreign key will first check the parent (emp_key) column to see that the manager exists (the emp_key exists in the emp_key column).

First, we need to create the foreign key.

alter table emp
add (constraint manger_fk foreign key (manager)
references emp (emp_key)
on delete set null);

We will discuss the ON DELETE part shortly, but first let’s look at the table and how the foreign key protects our data.

SQL> select
2 emp_key,
3 emp_last_name,
4 manager
5 from
6 emp;

EMP_KEY EMP_LAST_NAME MANAGER
--------- ------------------------------ ---------
E101 king
E102 jackson E101
E103 korn E101
E104 linus E101
E105 tokheim E101
E106 levender E101
E107 johnson E101
E108 baker E101
E109 coleman E101
E110 brannigan E101

Here, we see that King (emp_key E101) is the manager for all the employees. King is getting over worked, so the boss wants us to make Baker the manager for Linus and Johnson. We can do this with an easy UPDATE.

SQL> update emp
2 set manager = 'E108'
3 where emp_key in ('E104','E107');

2 rows updated.

SQL> select
2 emp_key,
3 emp_last_name,
4 manager
5 from
6 emp;

EMP_KEY EMP_LAST_NAME MANAGER
--------- ------------------------------ ---------
E101 king
E102 jackson E101
E103 korn E101
E104 linus E108
E105 tokheim E101
E106 levender E101
E107 johnson E108
E108 baker E101
E109 coleman E101
E110 brannigan E101

10 rows selected.

No problem there. Behind that UPDATE, the foreign key made sure that E108 was listed in the emp_key column before it allowed the UPDATE. Now, the boss has decided that he will be King’s manager. No problem, the boss’s emp_key is E000, so we just use another UPDATE.

SQL> update emp
2 set manager = 'E000'
3 where emp_key = 'E101';
update emp
*
ERROR at line 1:
ORA-02291: integrity constraint (PUBS.MANGER_FK) violated - parent key not Found

The boss cannot be listed as King’s manager, because he is not in the EMP table. More correctly, his emp_key is not a value in the emp_key column. If we add him to the table, then he can be King’s manager. This example showed a foreign key on a table. The foreign key can also be used to between tables. Look at the JOB and EMP tables.

The boss just made a new rule; no one can be an employee unless they have a job (kind of obvious). In other words, when they are assigned a job, it must already be in the JOB table. The EMP table contains a job key column that matches the job key column in the JOB table. Since we want to insure that the job is defined before the employee is assigned the job, the JOB table becomes the parent table and the EMP table the child table in this foreign key relationship.

Let’s create the constraint.

SQL> alter table emp
2 add (constraint job_fk foreign key (job_key)
3 references job (job_key)
4 on delete cascade);

Table altered.

Now, when INSERT or UPDATE the job key column in the EMP table, the foreign key constraint will check to insure that the job already exists in the JOB table (or at least the job key exist in the JOB table).

Foreign key constraints can also be disabled, enabled and dropped.

alter table emp disable constraint job_fk;

alter table emp enable constraint job_fk;

alter table emp drop constraint job_fk;

Now, let’s talk about the ON DELETE part of the constraint. When we created the foreign key constraint we included ON DELETE SET NULL or ON DELETE CASCADE. This clause tells the database what to do with the child records when the parent record is deleted. In the example above, we created the job_fk constraint with ON DELETE CASCADE. This will cause the database to cascade the deletes. If I go to the JOB table and DELETE a job, all the employees that have that job will also be deleted as the DELETE will cascade to the child rows. If we use the ON DELETE SET NULL, then when we delete the parent record, the child records with that value will be set to NULL. Why would we want to cascade foreign key deletes? One great example is the STATSPACK package that Oracle provides with the database. This package has over twenty tables, and it gathers performance statistics from the database and loads them into the tables.

The statistics are gathered periodically, say once every hour. As time goes by, the STATSPACK tables will grow very large. If I only want to maintain 30 days of data, I need to delete the data that is older than 30 days. Because STATSPACK creates it tables with a foreign key ON DELETE CASCADE to one main table called stats$snapshot, I can delete the old rows in that one table and all the data in the other tables will also be deleted.

delete from stats$snapshot where snap_time < sysdate - 30;

This one command deletes all the data over 30 days old from all the STATSPACK tables. This is the power of ON DELETE CASCADE.

Foreign keys will allow you to make a column NULL, even if there is not a NULL in the parent table.

SQL> update emp
2 set job_key = NULL
3 where emp_key = 'E106';

1 row updated.

This is not surprising given that ON DELETE SET NULL will, in fact, set a column to NULL when the parent value is deleted.

When you use a foreign key on multiple columns, you can end up with invalid data in your table if you are not careful. If I have a four column foreign key, the constraint is ensuring that the combined four values are in the parent table. If one of the values in the UPDATE/INSERT contains a NULL, then none of the other values are checked. So, if I created a multi-column foreign key on the EMP table that included the emp_key, job_key and pub_key to some parent table with those columns (the PUBS schema does not have such a table so this example is hypothetical) and inserted a row with the job_key set to NULL, the foreign key will not check the emp_key or the pub_key values. In this way, you could enter a row with an invalid emp_key (someone who is not an employee), an invalid pub_key (unknown publisher), and a NULL job_key, and the database will accept it.