Oracle Create 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.