oracle 主键和外键的一比较细…

文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm#SQLRF01111

 

Foreign KeyConstraint Example Thefollowing statement creates thedept_20 table anddefines and enables a foreign key on thedepartment_idcolumn that references the primary key on thedepartment_id column of thedepartmentstable:

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 thedepartments table.

 

However, employees can have null department numbers,meaning they are not assigned to any department.

---1、从文档此句话可以看出。当在子表（即存有外键值的表）可以存储外键列为空值，即insert和update语句可以往这些外键列里更新为空值。但如果DML要更新为具体的值，那么这些值必须和主表(父表)的值一致才能更新成功，否则会报错。

 

2、上述意思：如果在子表中的雇员拥有保存空的部门ID号，那么意味着他们都还没被分配部门。如果需要在子表里插入数据的同时，需要分配具体部门，那么需要将此字段增加NOTNULL约束。见下。

 

 To ensure that all employees areassigned to a department, you could create aNOTNULL constraint on thedepartment_idcolumn 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 thedepartmentstable as a primary or unique key.