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
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>
One-to-one using a primary-key and foreign-key relationship
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.
ORA-12991: column is referenced in a multi-column constraint
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>
Set primary key when declaring a column
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>
Use three columns as primary key
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.
Using a CREATE TABLE statement: create a table with primary key
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>
Violate the primary key and foreign key relation
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.