How to add primary key using ALTER TABLE in Oracle?




Syntax

A primary key can be defined on a single column or multiple columns. However, in Oracle, a primary key can be defined on up to 32 columns. That’s the limit.

To add a primary key constraint to an existing table using ALTER TABLE statement, here is the generic syntax:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

Example

We have table salaries:

CREATE TABLE salaries (
	emp_no NUMBER NOT NULL,
	salary NUMBER NOT NULL,
	from_date DATE NOT NULL,
	to_date DATE NOT NULL
);

Now we want to add a primary key on the column emp_no:

ALTER TABLE salaries
ADD CONSTRAINT salaries_pk PRIMARY KEY (emp_no);

Or specify the primary key on two columns: emp_no and from_date:

ALTER TABLE salaries
ADD CONSTRAINT salaries_pk PRIMARY KEY (emp_no, from_date);

---

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download for iOS