Oracle PL/SQL/Constraints/DELETE SET NULL
Create a foreign key "set null on delete" with more than one field
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>
Foreign Keys with "set null on delete"
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>