Oracle PL/SQL Tutorial/Table/FOREIGN KEY — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
м (1 версия)
 
(нет различий)

Текущая версия на 13:05, 26 мая 2010

Add constraint foreign key references

   <source lang="sql">

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.</source>


Adding a FOREIGN KEY Constraint

   <source lang="sql">

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></source>


A foreign key to reference itself

   <source lang="sql">

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.</source>


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>


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

   <source lang="sql">

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></source>


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.



   <source lang="sql">

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></source>


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

   <source lang="sql">

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></source>


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

   <source lang="sql">

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></source>


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.



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

Table altered. SQL> SQL> SQL> drop table account; Table dropped. SQL> SQL> drop table customer; Table dropped. SQL> SQL> SQL>SQL> SQL></source>


Violate a foreign key

   <source lang="sql">

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></source>