2.3. Foreign Keys

Suppose you want to make sure all employees belong to a valid department. This is called maintaining the referential integrity of your data. In simplistic database systems this would be implemented (if at all) by first looking at the dept table to check if a matching record exists, and then inserting or rejecting the new emp records. This approach has a number of problems and is very inconvenient. EnterpriseDB can make it easier for you.

We will be using a modified version of the emp table in this section in order to demonstrate the creation and use of foreign key constraints. Our modified emp table will look something like the following:

The new declaration of the tables would look like this:

CREATE TABLE emp (
     empno           NUMBER(4),
     ename           VARCHAR2(10),
     job             VARCHAR2(9),
     mgr             NUMBER(4),
     hiredate        DATE,
     sal             NUMBER(7,2),
     comm            NUMBER(7,2),
     deptno 	     NUMBER(2) CONSTRAINT emp_ref_deptno_fk
                     REFERENCES dept(deptno) );

Now try inserting an invalid record:

INSERT INTO emp VALUES (1234, 'TESTEMP', 'MANAGER', 7902, '20-JAN-85', 1000, NULL, 50);

ERROR:  insert or update on table "emp" violates foreign key constraint "emp_ref_deptno_fk"
DETAIL:  Key (deptno)=(50) is not present in table "dept".

The behavior of foreign keys can be finely tuned to your application. We will not go beyond this simple example in this tutorial, but just refer you to Chapter 4 for more information. Making correct use of foreign keys will definitely improve the quality of your database applications, so you are strongly encouraged to learn more about them.