primary key

                                            PRIMARY KEYS

In Oracle, a primary key is a single field or combination of fields that uniquely defines a record.

NOTE

The fields that are part of the primary key can’t contain a null value.

A table can have only one primary key.

In Oracle, a primary key cannot contain more than 32 columns.

A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement. 

Defining in CREATE TABLE

syntax:

CREATE TABLE table_name

( 

column1 datatype null/not null, 

column2 datatype null/not null,  ... 

CONSTRAINT constraint_name PRIMARY KEY (column1,... column_n)

);

Example

CREATE TABLE student

(

  student_id numeric(10) not null,

  student_name varchar2(50) not null,

  CONSTRAINT student_pk PRIMARY KEY (student_id)

);

Defining in ALTER TABLE STATEMENT

Syntax

ALTER TABLE table_name

ADD CONSTRAINT constraint_name PRIMARY KEY (column1,..column_n);

Example

ALTER TABLE student

ADD CONSTRAINT student_pk PRIMARY KEY (student_id);

Creating a primary key with more than one field

ALTER TABLE student

ADD CONSTRAINT student_pk PRIMARY KEY (student_id, student_name);

DROP PRIMARY KEY

Syntax

ALTER TABLE table_name

DROP CONSTRAINT constraint_name;

Example

ALTER TABLE student

 DROP CONSTRAINT student_pk;

DISABLE PRIMARY KEY

Syntax

ALTER TABLE table_name

DISABLE CONSTRAINT constraint_name;

Example

ALTER TABLE student

 DISABLE CONSTRAINT student_pk;

ENABLE PRIMARY KEY

Syntax

ALTER TABLE table_name

ENABLE CONSTRAINT constraint_name;

Example

ALTER TABLE student

ENABLE CONSTRAINT student_pk;