Use alter table to add foreign key with cascade delete : Add Primary Key « Constraints « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Constraints
  3. Add Primary Key

Use alter table to add foreign key with cascade delete

  

SQL>
SQL> -- Use alter table to add 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             supplier_name   varchar2(50)    not null
  5      );

Table created.

SQL>
SQL>
SQL> ALTER TABLE products
  2  add CONSTRAINT fk_supplier
  3    FOREIGN KEY (supplier_id)
  4    REFERENCES supplier(supplier_id)
  5    ON DELETE CASCADE;

Table altered.

SQL>
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>
SQL>








Related examples in the same category

1.Add Primary Key
2.Use alter table to add foreign key with cascade delete for more than one column
3.Alter a table to insert primary key and index
4.Add primary key and try to insert default value to it
5.DUP_VAL_ON_INDEX exception.
6.Alter table to add primary key
7.Alter table to primary key and check it in user_ind_columns and user_cons_columns
8.Joined primary key
9.Add primary key as the last statement
10.Setting primary key as declaring the column