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 UNIQUE Key Constraint

In Oracle, UNIQUE KEY constraint is used to ensure that all values in a column or combination of columns are unique or different. The UNIQUE KEY constraint provides uniqueness of column or combination of columns data. In Oracle, you can have more than one UNIQUE KEY constraints on each table.

Oracle UNIQUE Key Constraint example

The following Oracle, creates a UNIQUE Key constraint on the "custId" column when the "tblCustomer" table is created:

Example

CREATE TABLE tblCustomer (
    custId int NOT NULL,
    firstName varchar(255) NOT NULL,
    lastName varchar(255) NOT NULL,
    address varchar(255) NOT NULL,
	CONSTRAINT custid_unique UNIQUE (custId)
);

Oracle UNIQUE Key Constraint on multiple columns example

The following Oracle, creates a UNIQUE Key constraint on the "custId" and "firstName" columns when the "tblCustomer" table is created:

Example

CREATE TABLE tblCustomer (
    custId int NOT NULL,
    firstName varchar(255) NOT NULL,
    lastName varchar(255) NOT NULL,
    address varchar(255) NOT NULL,
	CONSTRAINT UC_Customer UNIQUE (custId,firstName)
);

Note:

In the above example, we are using UNIQUE constraint on "custId" and "firstName" columns to ensure that all values in the columns "custId" and "firstName" are unique or different.

Oracle UNIQUE Key Constraint on ALTER table example

The following Oracle, creates a UNIQUE Key constraint on the "custId" column when table already exists into the database:

Example

ALTER TABLE tblCustomer
ADD CONSTRAINT custid_unique UNIQUE (custId);

Oracle DROP a UNIQUE Key Constraint example

The following Oracle, dropping UNIQUE Key constraint "UC_Customer" from "tblCustomer" table:

Example

ALTER TABLE tblCustomer
DROP CONSTRAINT UC_Customer;