Oracle PL/SQL Tutorial/Table/FOREIGN KEY — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Текущая версия на 13:05, 26 мая 2010
Содержание
- 1 Add constraint foreign key references
- 2 Adding a FOREIGN KEY Constraint
- 3 A foreign key to reference itself
- 4 Disable foreign key
- 5 Many to many using a primary-key and foreign-key relationship
- 6 ON DELETE CASCADE clause with a FOREIGN KEY constraint
- 7 ORA-02270: no matching unique or primary key for this column-list
- 8 ORA-02298: cannot validate (JAVA2S.PRODUCT_ORDER_FK_PRODUCT) - parent keys not found
- 9 Use the ON DELETE SET NULL clause with a FOREIGN KEY constraint
- 10 Violate a foreign key
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>