Oracle PL/SQL/Constraints/Create Foreign Key

Add constraint foreign key references

  
SQL>
SQL> create table parts
  2  (part_no   number primary key,
  3   part_name varchar2(20),
  4   parent number);
Table created.
SQL>
SQL> insert into parts
  2  values (4, "part1" , 3)
  3  /
1 row created.
SQL>
SQL> insert into parts
  2  values (5, "part2" , 3)
  3  /
1 row created.
SQL>
SQL> insert into parts
  2  values (6, "part3" , 3)
  3  /
1 row created.
SQL>
SQL> insert into parts
  2  values (7, "part4" , 6)
  3  /
1 row created.
SQL>
SQL> insert into parts
  2  values (10, "part5" , null)
  3  /
1 row created.
SQL>
SQL> insert into parts
  2  values (2, "part6" , 10)
  3  /
1 row created.
SQL>
SQL> insert into parts
  2  values (3, "part7" , 10)
  3  /
1 row created.
SQL>
SQL> alter table parts
  2  add constraint fk_parent foreign key (parent) references parts;
Table altered.
SQL>
SQL>
SQL> drop table parts;
Table dropped.

Create a foreign key with more than one field

 

SQL>
SQL> --  create a foreign key with more than one field as in the example below:
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      );
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>     drop table supplier cascade constraints;
Table dropped.
SQL>
SQL>

Create table with foreign key

 
SQL>
SQL> -- create a foreign key
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>
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>

Declaring a Foreign Key

  
SQL>
SQL>
SQL> CREATE TABLE myTable (myTableID INT PRIMARY KEY NOT NULL,
  2                       Name VARCHAR(50));
Table created.
SQL>
SQL> CREATE TABLE Phone (
  2     PhoneID INT PRIMARY KEY NOT NULL,
  3     myTableID INT,
  4     PhoneNo VARCHAR(20),
  5     CONSTRAINT FID_FK FOREIGN KEY(myTableID)
  6        REFERENCES myTable(myTableID));
Table created.

Referential Integrity Constraints

  
SQL>
SQL> create table department (
  2      Dept_no  NUMBER(3) primary key,
  3      Dept_name   VARCHAR2(15),
  4      Location     VARCHAR2(25),
  5      CONSTRAINT Dept_name_ukey UNIQUE (Dept_Name,Location));
Table created.
SQL>
SQL>
SQL> create table employee (
  2  Employee_id    number (7),
  3  Last_name      varchar2(30),
  4  First_name     varchar2(30),
  5  Job            varchar2(15),
  6  Department_id  number (3) NOT NULL
  7                 Constraint dept_fkey REFERENCES department(dept_NO));
Table created.
SQL>
SQL> drop table employee cascade constraints;
Table dropped.
SQL> drop table department cascade constraints;
Table dropped.
SQL>

Violate a foreign key

  
SQL>
SQL> create table p ( pk  int primary key )
  2  /
Table created.
SQL> create table c
  2  ( fk  constraint c_fk
  3        references p(pk)
  4        deferrable
  5        initially immediate
  6  )
  7  /
Table created.
SQL> insert into p values ( 1 );
1 row created.
SQL> insert into c values ( 1 );
1 row created.
SQL>
SQL> update p set pk = 2;
update p set pk = 2
*
ERROR at line 1:
ORA-02292: integrity constraint (sqle.C_FK) violated - child record found

SQL>
SQL> set constraint c_fk deferred;
Constraint set.
SQL>
SQL> update p set pk = 2;
1 row updated.
SQL>
SQL> set constraint c_fk immediate;
set constraint c_fk immediate
*
ERROR at line 1:
ORA-02291: integrity constraint (sqle.C_FK) violated - parent key not found

SQL>
SQL> update c set fk = 2;
1 row updated.
SQL>
SQL> set constraint c_fk immediate;
Constraint set.
SQL>
SQL> drop table p;
drop table p
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> drop table c;
Table dropped.
SQL>
SQL>