syntax and example of primary key in oracle sql

Oracle PL/SQL PRIMARY KEY is single or group of column(s)/field(s) that can uniquely identify each record in a table.

An Oracle PL/SQL COMPOSITE PRIMARY KEY is the one which is made up of more than one fields or columns.

Important points about Oracle PL/SQL PRIMARY KEYS are:

• A table can have one and only one PRIMARY KEY.
• In Oracle SQL a PRIMARY KEY can be created by using maximum of 32 columns / fields.
• The columns / fields that are used to define a PRIMARY KEY on table cannot have NULL values.
• A PRIMARY KEY can be defined using CREATE TABLE or ALTER TABLE Statement.

---

Oracle PL/SQL PRIMARY KEY using CREATE TABLE Statement Syntax

CREATE TABLE table_name
   (column_name1 datatype NULL/NOT NULL
   ,column_name2 datatype NULL/NOT NULL
   ,column_name3 datatype NULL/NOT NULL
.
column_nameN datatype null/notnull
,CONSTRAINT constraint_name PRIMARY KEY (column_name1, column_name2, . , column_nameN );

---

Oracle PL/SQL PRIMARY KEY using ALTER TABLE Statement Syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column_name1,column_name2, . ,column_nameN);

---

Oracle PL/SQL PRIMARY KEY Examples

Oracle PL/SQL PRIMARY KEY – Using CREATE TABLE Statement

Suppose, we wish to create Oracle PL/SQL PRIMARY KEY constraint on ‘employee_id’ column of ‘employee’ table;

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Below CREATE TABLE Statement will define Oracle PL/SQL PRIMARY KEY on ‘employee’ table with “employee_id’ as PRIMARY KEY

CREATE TABLE employee
(employee_id        NUMBER(10)       NOT NULL
,employee_name      VARCHAR2(500)    NOT NULL
,salary             NUMBER(20)       NOT NULL
,department         VARCHAR2(300)    NOT NULL
,commission         NUMBER(20)
,constraint employee_pk PRIMARY KEY (employee_id);

Here with the help of the above SQL CREATE statement we have created a table named ‘employee’ that has 5 columns namely ‘employee_id’, ‘employee_name’, ‘department’, ‘salary’ and ‘commission’.  Also we are having the ‘employee_id’ column as the primary key for the ‘employee’ table.

In the above Oracle PL/SQL PRIMARY KEY example the SQL CREATE TABLE Statement will create a new table named ‘employee’ having a PRIMARY KEY on ‘employee_id’ column.

Let’s insert some data in the ‘employee’ table.

insert into employee
values (101,'Emp A',10000,'Sales',10);

insert into employee
values (102,'Emp B',20000,’IT’,20);

insert into employee
values (103,'Emp C',28000,'IT',20);

insert into employee
values (104,'Emp D',30000,'Support',NULL);

insert into employee
values (105,'Emp E',32000,'Sales',10);

The above SQL INSERT Statements will insert 5 rows in the ‘employee’ table.

Now, if we query the employee table as:

SELECT *
FROM employee;

We will get the following result:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Here we have successfully created ‘employee’ table with ‘employee_id’ as PRIMARY KEY and also inserted data in ‘employee’ table.

If we try to insert one more record of employee_id = 105 as:

insert into employee
values (105,'Emp F',40000,'Sales',30);

We get an error that unique constraint is getting violated and the data cannot be entered, this is because ‘employee_id’ is the primary key and for employee _id = 105 we already have a record in ‘employee’ table and hence one more record of the same key cannot be entered.

---

Oracle PL/SQL PRIMARY KEY – Using ALTER TABLE Statement

Suppose we have not created any Oracle PL/SQL PRIMARY KEY on ‘employee’ table earlier .

We can use the SQL ALTER TABLE Statement to define a new PRIMARY KEY on employee table.

For example, the below SQL ALTER TABLE Statement will create PRIMARY KEY on ‘employee_id’ column of employee table.

ALTER TABLE employee
ADD CONSTRAINT employee_pk PRIMARY KEY (employee_id);

We can always DROP, DISABLE and ENABLE an Oracle PL/SQL PRIMARY KEY.

---