Oracle PL/SQL/Constraints/DELETE CASCADE

Create a foreign key with a cascade delete with more than one field

   <source lang="sql">

SQL> -- create a foreign key with a cascade 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)     not null,
 4             supplier_name   varchar2(50)    not null,
 5             CONSTRAINT fk_supplier_comp
 6               FOREIGN KEY (supplier_id, supplier_name)
 7              REFERENCES supplier(supplier_id, supplier_name)
 8              ON DELETE CASCADE
 9      );

Table created. SQL> SQL> desc products;

Name                                                                                                  Null?    Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
PRODUCT_ID                                                                                            NOT NULL NUMBER(10)
SUPPLIER_ID                                                                                           NOT NULL NUMBER(10)
SUPPLIER_NAME                                                                                         NOT NULL 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>

      </source>