What is a primary key?
A primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a null value. A table can have only one primary key.
Note:
In Oracle, a primary key can not contain more than 32 columns.
A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
Using a CREATE TABLE statement
The syntax for creating a primary key using a CREATE TABLE statement is:
CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name PRIMARY KEY (column1, column2, . column_n)
);
For example:
CREATE TABLE supplier ( supplier_id numeric(10) not null, supplier_name varchar2(50) not null, contact_name varchar2(50), CONSTRAINT supplier_pk PRIMARY KEY (supplier_id) );
In this example, we've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field.
We could also create a primary key with more than one field as in the example below:
CREATE TABLE supplier ( supplier_id numeric(10) not null, supplier_name varchar2(50) not null, contact_name varchar2(50), CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name) );
Using an ALTER TABLE statement
The syntax for creating a primary key in an ALTER TABLE statement is:
ALTER TABLE table_name
add CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);
For example:
ALTER TABLE supplier
add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);
In this example, we've created a primary key on the existing supplier table called supplier_pk. It consists of the field called supplier_id.
We could also create a primary key with more than one field as in the example below:
ALTER TABLE supplier
add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name);
Drop a Primary Key
The syntax for dropping a primary key is:
ALTER TABLE table_name
drop CONSTRAINT constraint_name;
For example:
ALTER TABLE supplier
drop CONSTRAINT supplier_pk;
In this example, we're dropping a primary key on the supplier table called supplier_pk.
Disable a Primary Key
The syntax for disabling a primary key is:
ALTER TABLE table_name
disable CONSTRAINT constraint_name;
For example:
ALTER TABLE supplier
disable CONSTRAINT supplier_pk;
In this example, we're disabling a primary key on the supplier table called supplier_pk.
Enable a Primary Key
The syntax for enabling a primary key is:
ALTER TABLE table_name
enable CONSTRAINT constraint_name;
For example:
ALTER TABLE supplier
enable CONSTRAINT supplier_pk;
In this example, we're enabling a primary key on the supplier table called supplier_pk.
