PRIMARY KEY Constraint Example

Type Of PRIMARY KEY Constraint

Type Of PRIMARY KEY Constraint	Example
Column Level PRIMARY KEY Constraint	CREATE TABLE SamplePK01(SampID NUMBER(2) CONSTRAINT SamplePK01_SampID_PK PRIMARY KEY,SampName VARCHAR2(10),SampDate DATE);
Table Level PRIMARY KEY Constraint	CREATE TABLE SamplePK02(SampID NUMBER(2),SampName VARCHAR2(10),SampDate DATE,CONSTRAINT SamplePK02_SampID_PK PRIMARY KEY(SampID));
Composite PRIMARY KEY Constraint ,always declare in Table level	CREATE TABLE SamplePK05(SampID NUMBER(2), SampName VARCHAR2(10), SampDate DATE, CONSTRAINT SamplePK05_SampIDName_PK PRIMARY KEY(SampID, SampName) );

Now i am trying to insert data in  Column level PK Constraint table SamplePK01, PK is  SampID .

SQL> INSERT INTO SamplePK01 VALUES(1, 'SAMPLE01', SYSDATE);

1 row created.

SQL> INSERT INTO SamplePK01 VALUES(1, 'SAMPLE02', SYSDATE);

INSERT INTO SamplePK01 * ERROR at line 1: ORA-00001: unique constraint (SCOTT.SAMPLEPK01_SAMPID_PK) violated

SQL> INSERT INTO SamplePK01 VALUES(NULL, 'SAMPLE02', SYSDATE);

VALUES(NULL, 'SAMPLE02', SYSDATE) * ERROR at line 2: ORA-01400: cannot insert NULL into ("SCOTT"."SAMPLEPK01"."SAMPID")

Insert data Into  Table level PK Constraint. Use above insert script by changing the table name.


Insert data Into Composite PRIMARY KEY  Constraint table SamplePK05, PK is SampID, SampName.

SQL> INSERT INTO SamplePK05 VALUES(1, 'SAMPLE01', SYSDATE);

1 row created.

SQL> INSERT INTO SamplePK05 VALUES(1, 'SAMPLE02', SYSDATE);

1 row created.

SQL> INSERT INTO SamplePK05 VALUES(2, 'SAMPLE02', SYSDATE);

1 row created.

SQL> INSERT INTO SamplePK05 VALUES(NULL, 'SAMPLE03', SYSDATE);

VALUES(NULL, 'SAMPLE03', SYSDATE) * ERROR at line 2: ORA-01400: cannot insert NULL into ("SCOTT"."SAMPLEPK05"."SAMPID")

SQL> INSERT INTO SamplePK05 VALUES(3, NULL, SYSDATE);

VALUES(3, NULL, SYSDATE) * ERROR at line 2: ORA-01400: cannot insert NULL into ("SCOTT"."SAMPLEPK05"."SAMPNAME")