Oracle PL/SQL/Constraints/Create Primary Key

Материал из SQL эксперт
Перейти к: навигация, поиск

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>