Oracle PL/SQL/Constraints/Create Primary Key
Содержание
- 1 Create a foreign key "set null on delete" with more than one field, and use desc to check
- 2 One-to-one using a primary-key and foreign-key relationship
- 3 ORA-12991: column is referenced in a multi-column constraint
- 4 Set primary key when declaring a column
- 5 Use three columns as primary key
- 6 Using a CREATE TABLE statement: create a table with primary key
- 7 Violate the primary key and foreign key relation
Create a foreign key "set null on delete" with more than one field, and use desc to check
<source lang="sql">
SQL> SQL> SQL> SQL> -- Create a foreign key "set null on delete" with more than one field 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), 4 supplier_name varchar2(50), 5 CONSTRAINT fk_supplier_comp 6 FOREIGN KEY (supplier_id, supplier_name) 7 REFERENCES supplier(supplier_id, supplier_name) 8 ON DELETE SET NULL 9 );
Table created. SQL> SQL> SQL> desc products;
Name Null? Type ----------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------- PRODUCT_ID NOT NULL NUMBER(10) SUPPLIER_ID NUMBER(10) SUPPLIER_NAME 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> SQL> drop table supplier cascade constraints; Table dropped. SQL> SQL>
</source>
One-to-one using a primary-key and foreign-key relationship
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE Office
2 (office_id VARCHAR2(10) NOT NULL, 3 building_name VARCHAR2(20), 4 PRIMARY KEY (office_id));
Table created. SQL> SQL> CREATE TABLE Programmer
2 (lect_id VARCHAR2(10) NOT NULL, 3 lect_name VARCHAR2(20), 4 office_id VARCHAR2(10), 5 PRIMARY KEY (lect_id), 6 FOREIGN KEY (office_id) REFERENCES Office (office_id) 7 ON DELETE CASCADE);
Table created. SQL> SQL> drop table Office cascade constraints; Table dropped. SQL> drop table Programmer cascade constraints; Table dropped.
</source>
ORA-12991: column is referenced in a multi-column constraint
<source lang="sql">
SQL> create table registrations
2 ( attendee NUMBER(4) 3 , course VARCHAR2(6) 4 , begindate DATE 5 , evaluation NUMBER(1) 6 , constraint R_PK primary key (attendee,course,begindate) 7 ) ;
Table created. SQL> SQL> SQL> SQL> SQL> alter table registrations
2 drop column begindate; drop column begindate *
ERROR at line 2: ORA-12991: column is referenced in a multi-column constraint
SQL> SQL> SQL> drop table registrations; Table dropped. SQL> SQL> SQL>
</source>
Set primary key when declaring a column
<source lang="sql">
SQL> SQL> create table t
2 ( x int primary key , 3 y date, 4 z clob ) 5 /
Table created. SQL> SQL> drop table t; Table dropped. SQL>
</source>
Use three columns as primary key
<source lang="sql">
SQL> SQL> create table registrations
2 ( attendee NUMBER(4) 3 , course VARCHAR2(6) 4 , begindate DATE 5 , evaluation NUMBER(1) 6 , constraint R_PK primary key (attendee,course,begindate) 7 ) ;
Table created. SQL> SQL> insert into registrations values (2,"SQL",date "2009-04-12",4 ); 1 row created. SQL> insert into registrations values (14,"SQL",date "2009-04-12",5 ); 1 row created. SQL> insert into registrations values (6,"SQL",date "2009-04-12",4 ); 1 row created. SQL> insert into registrations values (11,"SQL",date "2009-04-12",2 ); 1 row created. SQL> insert into registrations values (8,"SQL",date "2009-10-04",NULL); 1 row created. SQL> insert into registrations values (9,"SQL",date "2009-10-04",3 ); 1 row created. SQL> insert into registrations values (13,"SQL",date "2009-10-04",4 ); 1 row created. SQL> insert into registrations values (13,"SQL",date "2009-12-13",NULL); 1 row created. SQL> insert into registrations values (6,"SQL",date "2009-12-13",NULL); 1 row created. SQL> insert into registrations values (3,"OAU",date "2009-08-10",4 ); 1 row created. SQL> SQL> select evaluation
2 from registrations 3 where attendee = 8 4 order by evaluation;
EVALUATION
SQL> SQL> drop table registrations; Table dropped.
</source>
Using a CREATE TABLE statement: create a table with primary key
<source lang="sql">
SQL> SQL> --Using a CREATE TABLE statement 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> 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 supplier; Table dropped. SQL> SQL>
</source>
Violate the primary key and foreign key relation
<source lang="sql">
SQL> SQL> create table parent( pk int,constraint parent_pk primary key(pk) ); Table created. SQL> create table child ( fk,constraint child_fk foreign key(fk)
2 references parent deferrable );
Table created. SQL> /
SQL> insert into parent values( 1 ); 1 row created. SQL> insert into child values( 1 ); 1 row created. SQL> commit; Commit complete. SQL> update parent set pk = 2; update parent set pk = 2
ERROR at line 1: ORA-02292: integrity constraint (SYS.CHILD_FK) violated - child record found
SQL> update child set fk = 2; update child set fk = 2
ERROR at line 1: ORA-02291: integrity constraint (SYS.CHILD_FK) violated - parent key not found
SQL> set constraints child_fk deferred; Constraint set. SQL> update parent set pk=2; 1 row updated. SQL> select * from parent;
PK
2
SQL> select * from child;
FK
1
SQL> commit; commit
ERROR at line 1: ORA-02091: transaction rolled back ORA-02292: integrity constraint (SYS.CHILD_FK) violated - child record found
SQL> set constraints child_fk deferred; Constraint set. SQL> update parent set pk=2; 1 row updated. SQL> select * from parent;
PK
2
SQL> select * from child;
FK
1
SQL> set constraints child_fk immediate; set constraints child_fk immediate
ERROR at line 1: ORA-02291: integrity constraint (SYS.CHILD_FK) violated - parent key not found
SQL> update child set fk = 2; 1 row updated. SQL> set constraints child_fk immediate; Constraint set. SQL> commit; Commit complete. SQL> select * from parent;
PK
2
SQL> select * from child;
FK
2
SQL> SQL> drop table child cascade constraints; Table dropped. SQL> drop table parent cascade constraints; Table dropped.
</source>