Primary Key Constraint in Oracle

Primary Key Constraint:  it is a column or set of columns that uniquely identifies a row and doesn’t allow null values (i.e. column can’t be left blank).

Features:

  1. Primary key does not allow duplicate values.
  2. It does not allow null values.
  3. Primary key can’t be LONG or LONG RAW data type.
  4. Only one primary key is allowed per table.
  5. When primary key is created, unique index is created automatically.
  6. One table can combine upto 16 columns in a composite primary key.
  7. Each table should have primary key, it is recommended but not mandatory.

 

Primary Key Constraint at column level:

Syntax:

<ColumnName> <DataType>(<Size>) Primary Key


SQL Command To Create PK:

   create table emp(

   ename varchar2(10),

   id number(10) primary key,

   job varchar2(10),

   depid number(10)

)

Primary Key Constraint at Table level (Unamed Composite Primary key):

This is a composite primary key mapped to two columns ename and id. Since it maps across columns, so that described at table level.

Syntax:

Primary key (<Column Name>, <Column Name>)


SQL Command To Create PK:

create table emp(

ename varchar2(10),

id number(10),

job varchar2(10),

depid number(10),

Primary key (ename,id)

)

Named Composite Primary key:


SQL Command To Create PK:

Create table emp(

ename varchar2(10),

id number(10),

job varchar2(10),

depid number(10),

   CONSTRAINT eid_pk Primary key (ename,id)

)

ALTER Command for Primary Key:

Drop a PK Constraint:

 ALTER TABLE emp DROP CONSTRAINT eid_pk; Add a PK Constraint:

     Add a PK Constraint:

 ALTER TABLE emp ADD CONSTRAINT eid_pk PRIMARY KEY (ename,id);

Disabling the PK constraint

ALTER TABLE emp DISABLE PRIMARY KEY;

Enabling the PK constraint

ALTER TABLE emp ENABLE PRIMARY KEY;

 

View TO Check Primary Key Constraints on a table:

USER_CONSTRAINTS


SQL Command To Check PK:

SELECT constraint_name, constraint_type

  FROM user_constraints

 WHERE table_name = '<TABLE_NAME>';

 

Some Important facts About Primary Key:

 

  1. Primary Key constraints are different from unique index.
  2. An index (unique or non unique) is always associated with Enabled primary key and if a primary key is disabled then it won’t be associated with an index.
  3. If a primary key is disabled, associated index created by oracle with creation of PK, will be dropped automatically.
  4. Once we enable primary key constraint, oracle creates automatically a new index with same name as primary key.

 

 

 

Source: Internet+self

You can skip to the end and leave a response. Pinging is currently not allowed.

One Response to “Primary Key Constraint in Oracle”

  1. anonymous says:

    nice blog!!

Leave a Reply