Oracle PL/SQL/Constraints/Create 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>
Create a foreign key with more than one field
<source lang="sql">
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>
</source>
Create table with foreign key
<source lang="sql">
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>
</source>
Declaring a Foreign Key
<source lang="sql">
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.
</source>
Referential Integrity Constraints
<source lang="sql">
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>
</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>
