Oracle sql, 外鍵，級聯

--外鍵，級聯 
CREATE TABLE departments( 
  department_id NUMBER(4) CONSTRAINT department_id_id_u  UNIQUE, 
  department_name VARCHAR2(20) 
); 
DESC departments; 
INSERT INTO departments(department_id,department_name) 
VALUES(1,'組織部'); 
INSERT INTO departments(department_id,department_name) 
VALUES(2,'财務部'); 
SELECT * FROM departments;

CREATE TABLE dept_20( 
  employee_id NUMBER(4) PRIMARY KEY, 
  last_name VARCHAR2(10), 
  manager_id NUMBER(4) CONSTRAINT fk_mgr 
             REFERENCEs employees ON DELETE SET NULL,--要求emplyees這個表有主鍵 
  department_id NUMBER(2) CONSTRAINT fk_deptno 
                REFERENCES departments(department_id) 
                ON DELETE CASCADE 
); 
SELECT * FROM dept_20; 
INSERT INTO dept_20(EMPLOYEE_ID,LAST_NAME,MANAGER_ID,department_id) 
VALUES(1,'SMITH',1,2);

DROP TABLE dept_20;

CREATE TABLE employees( 
  mrg_id NUMBER(4) PRIMARY KEY, 
  mrg_name VARCHAR2(20) 
); 
INSERT INTO employees(MRG_ID,MRG_NAME) 
VALUES(1,'M1'); 
SELECT * FROM employees; 
DROP TABLE employees; 
--級聯删除。dept_20表中departemnt_id為2的也會跟着被删除。 
DELETE FROM departments WHERE DEPARTMENT_ID=2; 
--級聯設定null。dept_20表中的manager_id會被設定為NULL值。 
DELETE FROM employees WHERE mrg_id=1;