в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:05:03Z

1 версия

Новая страница

== 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==

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



<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==

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



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

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Table/FOREIGN KEY - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия