Oracle PL/SQL/Constraints/Create Primary Key

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.