If you want to make sure that all employees belong to a valid department, you must maintain referential integrity of the data. To maintain referential integrity for simplistic database systems, check whether the dept table contains a matched record and insert or reject a new employee record. This approach causes a number of problems and is not easy to use. PolarDB databases compatible with Oracle can simplify your data management.
A modified version of the emp table presented in section 2.1.2 is shown in this section. A foreign key constraint is added to the version. The following example shows the modified emp table:
CREATE TABLE emp ( empno NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY, 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_dept_fk REFERENCES dept(deptno) );
If an attempt is made to execute the following INSERT statement in the sample emp table, the foreign key constraint emp_ref_dept_fk makes sure that department 50 exists in the dept table. This department does not exist, so the statement is rejected.
INSERT INTO emp VALUES (8000,'JONES','CLERK',7902,'17-AUG-07',1200,NULL,50); ERROR: insert or update on table "emp" violates foreign key constraint "emp_ref_dept_fk" DETAIL: Key (deptno)=(50) is not present in table "dept".
The behavior of foreign keys can be finely tuned based on your application. The correct use of foreign keys improves the performance of your database applications. We recommend that you improve the use of foreign keys.