Oracle PL/SQL/Constraints/Drop Foreign Key

Disable foreign key

   
SQL>
SQL> create table it
  2   (c1 number primary key,
  3    c2 number,
  4    c3 number);

SQL>
SQL> alter table it add constraint it_fk foreign key (c2) references emp disable;

SQL>
SQL>
SQL> select object_name as name, object_id
  2  from user_objects
  3  where object_name in ("T", "IT");
IT                                                                                                                            14971
1 row selected.

Drop the foreign key

 

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)     not null,
  4             CONSTRAINT fk_supplier
  5               FOREIGN KEY (supplier_id)
  6               REFERENCES supplier(supplier_id)
  7      );
Table created.
SQL>
SQL>
SQL> -- If we then wanted to drop the foreign key called fk_supplier, we could execute the following command:
SQL>
SQL>     ALTER TABLE products
  2      drop CONSTRAINT fk_supplier;
Table altered.
SQL>
SQL>    drop table supplier cascade constraint;
Table dropped.
SQL>    drop table products cascade constraint;
Table dropped.
SQL>

Foreign key with cascade delete

 

SQL> -- foreign key with cascade 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)     not null,
  4             CONSTRAINT fk_supplier
  5               FOREIGN KEY (supplier_id)
  6              REFERENCES supplier(supplier_id)
  7              ON DELETE CASCADE
  8      );
Table created.
SQL>
SQL>     desc products;
 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
 PRODUCT_ID                                                                                            NOT NULL NUMBER(10)
 SUPPLIER_ID                                                                                           NOT NULL 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>