Oracle PL/SQL Tutorial/Table/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.

Adding a FOREIGN KEY Constraint

SQL>
SQL>
SQL> -- create demo table
SQL> create table Customer(
  2    id         NUMBER(3) primary key,
  3    NAME       VARCHAR2(15 BYTE)
  4  )
  5  /
Table created.
SQL>
SQL> create table account (
  2    id         NUMBER(3),
  3    type       VARCHAR2(20 BYTE)
  4  )
  5  /
Table created.
SQL>
SQL>
SQL> ALTER TABLE account
  2  ADD CONSTRAINT fk
  3  customerid REFERENCES customer(id);
Table altered.
SQL>
SQL>
SQL> drop table  account;
Table dropped.
SQL>
SQL> drop table customer;
Table dropped.
SQL>
SQL>
SQL>
SQL>
SQL>

A foreign key to reference itself

SQL>
SQL> create table employees
  2  ( empno      NUMBER(4)    constraint E_PK
  3                            primary key
  4                            constraint E_EMPNO_CHK
  5                            check (empno > 7000)
  6  , mgr        NUMBER(4)    constraint E_MGR_FK
  7                            references employees) ;
Table created.
SQL>
SQL> drop table employees;
Table dropped.

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.

Many to many using a primary-key and foreign-key relationship

SQL> CREATE TABLE Course
  2     (course_id        VARCHAR2(10) NOT NULL,
  3      course_name      VARCHAR2(20),
  4      PRIMARY KEY (course_id));
Table created.
SQL>
SQL> CREATE TABLE emp
  2     (stud_id    VARCHAR2(10) NOT NULL,
  3      stud_name  VARCHAR2(20),
  4      PRIMARY KEY (stud_id));
Table created.
SQL>
SQL> CREATE TABLE Enrolls_in
  2     (course_id  VARCHAR2(10) NOT NULL,
  3      stud_id    VARCHAR2(10) NOT NULL,
  4      PRIMARY KEY (course_id, stud_id),
  5      FOREIGN KEY (course_id) REFERENCES Course (course_id)
  6      ON DELETE CASCADE,
  7      FOREIGN KEY (stud_id) REFERENCES emp (stud_id)
  8      ON DELETE CASCADE);
Table created.
SQL>
SQL> drop table Course cascade constraints;
Table dropped.
SQL> drop table emp cascade constraints;
Table dropped.
SQL> drop table Enrolls_in;
Table dropped.
SQL>

ON DELETE CASCADE clause with a FOREIGN KEY constraint

ON DELETE CASCADE clause with a FOREIGN KEY constraint specifies that when a row in the parent table is deleted, any matching rows in the child table are also deleted.

SQL>
SQL>
SQL> -- create demo table
SQL> create table Customer(
  2    id         NUMBER(3) primary key,
  3    NAME       VARCHAR2(15 BYTE)
  4  )
  5  /
Table created.
SQL>
SQL> create table account (
  2    id         NUMBER(3),
  3    type       VARCHAR2(20 BYTE)
  4  )
  5  /
Table created.
SQL>
SQL>
SQL> ALTER TABLE account
  2  ADD CONSTRAINT fk
  3  customerid REFERENCES customer(id) ON DELETE CASCADE;
Table altered.
SQL>
SQL>
SQL> drop table  account;
Table dropped.
SQL>
SQL> drop table customer;
Table dropped.
SQL>

ORA-02270: no matching unique or primary key for this column-list

SQL>
SQL>
SQL> CREATE TABLE employee (
  2  id                             number,
  3  name                           varchar(100),
  4  birth_date                     date,
  5  gender                         varchar2(30) );
Table created.
SQL>
SQL> CREATE TABLE employee_evaluation (
  2  id                             number,
  3  title                          varchar2(100),
  4  written_date                   date );
Table created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> ALTER TABLE employee_evaluation ADD
  2  CONSTRAINT  employee_evaluation_fk1
  3  FOREIGN KEY        (id)
  4  REFERENCES  employee (id);
REFERENCES  employee (id)
                      *
ERROR at line 4:
ORA-02270: no matching unique or primary key for this column-list

SQL>
SQL>
SQL> drop table employee cascade constraint;
Table dropped.
SQL>
SQL> drop table employee_evaluation cascade constraint;
Table dropped.
SQL>

ORA-02298: cannot validate (JAVA2S.PRODUCT_ORDER_FK_PRODUCT) - parent keys not found

SQL>
SQL> CREATE TABLE product (
  2       product_name     VARCHAR2(25) PRIMARY KEY,
  3       product_price    NUMBER(4,2),
  4       quantity_on_hand NUMBER(5,0),
  5       last_stock_date  DATE
  6       );
Table created.
SQL>
SQL> CREATE TABLE product_order (
  2       product_name  VARCHAR2(25),
  3       salesperson   VARCHAR2(3),
  4       order_date DATE,
  5       quantity      NUMBER(4,2)
  6       );
Table created.
SQL>
SQL>
SQL> INSERT INTO product_order VALUES ("Product 1", "CA", "14-JUL-03", 1);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 2", "BB", "14-JUL-03", 75);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 3", "GA", "14-JUL-03", 2);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 4", "GA", "15-JUL-03", 8);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 5", "LB", "15-JUL-03", 20);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 6", "CA", "16-JUL-03", 5);
1 row created.
SQL> INSERT INTO product_order VALUES ("Product 7", "CA", "17-JUL-03", 1);
1 row created.
SQL>
SQL>
SQL> INSERT INTO product VALUES ("Product 1", 99,  1,    "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Product 2", 75,  1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product 3", 50,  100,  "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Product 4", 25,  10000, null);
1 row created.
SQL> INSERT INTO product VALUES ("Product 5", 9.95,1234, "15-JAN-04");
1 row created.
SQL> INSERT INTO product VALUES ("Product 6", 45,  1,    TO_DATE("December 31, 2008, 11:30 P.M.","Month dd, YYYY, HH:MI P.M."));
1 row created.
SQL>
SQL> ALTER TABLE product_order
  2       ADD CONSTRAINT product_order_fk_product FOREIGN KEY
  3       (product_name) REFERENCES product;
     ADD CONSTRAINT product_order_fk_product FOREIGN KEY
                    *
ERROR at line 2:
ORA-02298: cannot validate (sqle.PRODUCT_ORDER_FK_PRODUCT) - parent keys not found

SQL>
SQL>
SQL>
SQL> drop table product;
Table dropped.
SQL> drop table product_order;
Table dropped.
SQL>
SQL>
SQL>

Use the ON DELETE SET NULL clause with a FOREIGN KEY constraint

Specify that when a row in the parent table is deleted, the foreign key column for the row (or rows) in the child table is set to null.

SQL>
SQL> -- create demo table
SQL> create table Customer(
  2    id         NUMBER(3) primary key,
  3    NAME       VARCHAR2(15 BYTE)
  4  )
  5  /
Table created.
SQL>
SQL> create table account (
  2    id         NUMBER(3),
  3    type       VARCHAR2(20 BYTE)
  4  )
  5  /
Table created.
SQL>
SQL>
SQL> ALTER TABLE account
  2  ADD CONSTRAINT fk
  3  customerid REFERENCES customer(id) ON DELETE SET NULL;
Table altered.
SQL>
SQL>
SQL> drop table  account;
Table dropped.
SQL>
SQL> drop table customer;
Table dropped.
SQL>
SQL>
SQL>SQL>
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>