Oracle PL/SQL/Constraints/Drop Foreign Key

Disable foreign key

   <source lang="sql">
  

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.

 </source>
   
  

Drop the foreign key

   <source lang="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      );

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>

 </source>
   
  

Foreign key with cascade delete

   <source lang="sql">

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>

 </source>