文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm#SQLRF01111
Foreign KeyConstraint Example dept_20
table anddefines and enables a foreign key on the department_id
column that references the primary key on thedepartment_id
column of thedepartments
table:
CREATE TABLE dept_20 (employee_id NUMBER(4), last_name VARCHAR2(10), job_id VARCHAR2(9), manager_id NUMBER(4), hire_date DATE, salary NUMBER(7,2), commission_pct NUMBER(7,2), department_id CONSTRAINT fk_deptno REFERENCES departments(department_id) );
The constraint fk_deptno
ensures that alldepartments given for employees in thedept_20
tableare present in the departments
table.
However, employees can have null department numbers,meaning they are not assigned to any department.
---1、从文档此句话可以看出。当在子表(即存有外键值的表)可以存储外键列为空值,即insert和update语句可以往这些外键列里更新为空值。但如果DML要更新为具体的值,那么这些值必须和主表(父表)的值一致才能更新成功,否则会报错。
2、上述意思:如果在子表中的雇员拥有保存空的部门ID号,那么意味着他们都还没被分配部门。如果需要在子表里插入数据的同时,需要分配具体部门,那么需要将此字段增加NOTNULL约束。见下。
NOT
NULL
constraint on the department_id
column in thedept_20
table in addition to theREFERENCES
constraint.
Before you define and enable this constraint, you must defineand enable a constraint that designates thedepartment_id
column of thedepartments
table as a primary or unique key.