Oracle PL/SQL/Constraints/Create 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.
Create a foreign key with more than one field
SQL>
SQL> -- create a foreign key with more than one field as in the example below:
SQL>
SQL> CREATE TABLE supplier
2 ( supplier_id numeric(10) not null,
3 supplier_name varchar2(50) not null,
4 contact_name varchar2(50),
5 CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
6 );
Table created.
SQL>
SQL>
SQL> CREATE TABLE products
2 ( product_id numeric(10) not null,
3 supplier_id numeric(10) not null,
4 supplier_name varchar2(50) not null,
5 CONSTRAINT fk_supplier_comp
6 FOREIGN KEY (supplier_id, supplier_name)
7 REFERENCES supplier(supplier_id, supplier_name)
8 );
Table created.
SQL>
SQL> desc products;
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
PRODUCT_ID NOT NULL NUMBER(10)
SUPPLIER_ID NOT NULL NUMBER(10)
SUPPLIER_NAME NOT NULL VARCHAR2(50)
SQL> desc supplier;
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
SUPPLIER_ID NOT NULL NUMBER(10)
SUPPLIER_NAME NOT NULL VARCHAR2(50)
CONTACT_NAME VARCHAR2(50)
SQL>
SQL> drop table products cascade constraints;
Table dropped.
SQL> drop table supplier cascade constraints;
Table dropped.
SQL>
SQL>
Create table with foreign key
SQL>
SQL> -- create a foreign key
SQL>
SQL> CREATE TABLE supplier
2 ( supplier_id numeric(10) not null,
3 supplier_name varchar2(50) not null,
4 contact_name varchar2(50),
5 CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
6 );
Table created.
SQL>
SQL>
SQL> CREATE TABLE products
2 ( product_id numeric(10) not null,
3 supplier_id numeric(10) not null,
4 CONSTRAINT fk_supplier
5 FOREIGN KEY (supplier_id)
6 REFERENCES supplier(supplier_id)
7 );
Table created.
SQL>
SQL>
SQL>
SQL> desc products;
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
PRODUCT_ID NOT NULL NUMBER(10)
SUPPLIER_ID NOT NULL NUMBER(10)
SQL> desc supplier;
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
SUPPLIER_ID NOT NULL NUMBER(10)
SUPPLIER_NAME NOT NULL VARCHAR2(50)
CONTACT_NAME VARCHAR2(50)
SQL>
SQL> drop table products cascade constraints;
Table dropped.
SQL>
SQL> drop table supplier cascade constraints;
Table dropped.
SQL>
Declaring a Foreign Key
SQL>
SQL>
SQL> CREATE TABLE myTable (myTableID INT PRIMARY KEY NOT NULL,
2 Name VARCHAR(50));
Table created.
SQL>
SQL> CREATE TABLE Phone (
2 PhoneID INT PRIMARY KEY NOT NULL,
3 myTableID INT,
4 PhoneNo VARCHAR(20),
5 CONSTRAINT FID_FK FOREIGN KEY(myTableID)
6 REFERENCES myTable(myTableID));
Table created.
Referential Integrity Constraints
SQL>
SQL> create table department (
2 Dept_no NUMBER(3) primary key,
3 Dept_name VARCHAR2(15),
4 Location VARCHAR2(25),
5 CONSTRAINT Dept_name_ukey UNIQUE (Dept_Name,Location));
Table created.
SQL>
SQL>
SQL> create table employee (
2 Employee_id number (7),
3 Last_name varchar2(30),
4 First_name varchar2(30),
5 Job varchar2(15),
6 Department_id number (3) NOT NULL
7 Constraint dept_fkey REFERENCES department(dept_NO));
Table created.
SQL>
SQL> drop table employee cascade constraints;
Table dropped.
SQL> drop table department cascade constraints;
Table dropped.
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>