Oracle PL/SQL Tutorial/Table/FOREIGN KEY

Материал из SQL эксперт

Перейти к: навигация, поиск

Содержание

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>