Oracle PL/SQL/Constraints/DELETE SET NULL
Create a foreign key "set null on delete" with more than one field
<source lang="sql">
SQL> SQL> SQL> SQL> -- Create a foreign key "set null on delete" with more than one field SQL> SQL> CREATE TABLE supplier
2 ( supplier_id numeric(10) not null, 3 supplier_name varchar2(50) not null, 4 contact_name varchar2(50), 5 CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name) 6 );
Table created. SQL> SQL> SQL> CREATE TABLE products
2 ( product_id numeric(10) not null, 3 supplier_id numeric(10), 4 supplier_name varchar2(50), 5 CONSTRAINT fk_supplier_comp 6 FOREIGN KEY (supplier_id, supplier_name) 7 REFERENCES supplier(supplier_id, supplier_name) 8 ON DELETE SET NULL 9 );
Table created. SQL> SQL> SQL> desc products;
Name Null? Type ----------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------- PRODUCT_ID NOT NULL NUMBER(10) SUPPLIER_ID NUMBER(10) SUPPLIER_NAME VARCHAR2(50)
SQL> desc supplier;
Name Null? Type ----------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------- SUPPLIER_ID NOT NULL NUMBER(10) SUPPLIER_NAME NOT NULL VARCHAR2(50) CONTACT_NAME VARCHAR2(50)
SQL> SQL> drop table products cascade constraints; Table dropped. SQL> SQL> drop table supplier cascade constraints; Table dropped. SQL> SQL>
</source>
Foreign Keys with "set null on delete"
<source lang="sql">
SQL> SQL> -- Foreign Keys with "set null on delete" SQL> SQL> CREATE TABLE supplier
2 ( supplier_id numeric(10) not null, 3 supplier_name varchar2(50) not null, 4 contact_name varchar2(50), 5 CONSTRAINT supplier_pk PRIMARY KEY (supplier_id) 6 );
Table created. SQL> SQL> SQL> CREATE TABLE products
2 ( product_id numeric(10) not null, 3 supplier_id numeric(10), 4 CONSTRAINT fk_supplier 5 FOREIGN KEY (supplier_id) 6 REFERENCES supplier(supplier_id) 7 ON DELETE SET NULL 8 );
Table created. SQL> SQL> desc products;
Name Null? Type ----------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------- PRODUCT_ID NOT NULL NUMBER(10) SUPPLIER_ID NUMBER(10)
SQL> desc supplier;
Name Null? Type ----------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------- SUPPLIER_ID NOT NULL NUMBER(10) SUPPLIER_NAME NOT NULL VARCHAR2(50) CONTACT_NAME VARCHAR2(50)
SQL> SQL> drop table products cascade constraints; Table dropped. SQL> SQL> drop table supplier cascade constraints; Table dropped. SQL> SQL>
</source>
