Oracle Tutorial

What is Oracle
SQL Keywords

Oracle Wildcards

Oracle Wildcards

Oracle Aliases

Oracle Aliases

MySQL Tutorial

MySQL Tutorial

PL/SQL Tutorial

PL/SQL Tutorial

Oracle Interview Questions

Oracle Interview Questions and Answers


Oracle PRIMARY KEY Constraint

In Oracle, PRIMARY KEY constraint is used to ensure that all values in a column are unique and cannot contain NULL values. The PRIMARY KEY constraint forces the column to always accept a UNIQUE value without NULL. In Oracle, you can have only one PRIMARY KEY constraints on each table. The PRIMARY KEY constraint can be create on single column or group of multiple columns.

Oracle PRIMARY KEY Constraint example on create table

The following Oracle, creates PRIMARY KEY on "custId" column when the "tblCustomer" table is created:

Example

CREATE TABLE tblCustomer (
    custId int NOT NULL PRIMARY KEY,
    firstName varchar(255) NOT NULL,
    lastName varchar(255) NOT NULL,
    address varchar(255) NOT NULL
);

OR

CREATE TABLE tblCustomer (
    custId int NOT NULL,
    firstName varchar(255) NOT NULL,
    lastName varchar(255) NOT NULL,
    address varchar(255) NOT NULL,
	PRIMARY KEY (custId)
);

Note:

In the above example, we are creating new table called "tblCustomer" using PRIMARY KEY constraint. The PRIMARY KEY constraint enforces column "custId" to always accept UNIQUE value without NULL.

Oracle PRIMARY KEY Constraint example on ALTER table

The following Oracle, creates PRIMARY KEY on existing table:

Example

ALTER TABLE tblCustomer
ADD PRIMARY KEY (custId);

OR

ALTER TABLE tblCustomer
ADD CONSTRAINT PK_Customer PRIMARY KEY (custId,firstName);

Oracle DROP a PRIMARY KEY Constraint example

The following Oracle, dropping PRIMARY KEY constraint from "tblCustomer" table:

Example

ALTER TABLE tblCustomer
DROP PRIMARY KEY;

OR

ALTER TABLE tblCustomer
DROP CONSTRAINT PK_Customer;