• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / sql / UNIQUE CONSTRAINT in Oracle SQL – PLSQL

UNIQUE CONSTRAINT in Oracle SQL – PLSQL

October 31, 2012 by techhoneyadmin

In Oracle SQL / PLSQL a UNIQUE CONSTRAINT is single or group of column(s)/field(s) that can uniquely identify each record in a table.

Some of the column(s) / field(s) of unique constraint can have NULL values until the combination can uniquely identify each record of table.

Important points about UNIQUE CONSTRAINT in Oracle SQL / PLSQL:

  1. In Oracle SQL / PLSQL a UNIQUE CONSTRAINT can be created by using maximum of 32 columns / fields.
  2. A UNIQUE CONSTRAINT can be defined in CREATE TABLE or ALTER TABLE statement.

Syntax to create a UNIQUE CONSTRAINT in CREATE TABLE statement is:

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/NOT NULL
,CONSTRAINT constraint_name UNIQUE (column_name1, column_name2, . . . ,column_nameN );

Syntax to create a UNIQUE CONSTRAINT in ALTER TABLE statement is:

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

Let’s take an example for understanding:

Scenario 1:

Step 1: Suppose we want to create a table named ‘employee’ in the database as shown below, with ‘employee_id’ column having UNIQUE CONSTRAINT.

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

We can achieve the same as:

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 UNIQUE (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 UNIQUE CONSTRAINT on ‘employee_id’ column of the ‘employee’ table.

Step 2: Inserting the 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 column ‘employee_id’ having UNIQUE CONSTRAINT 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’ column has UNIQUE CONSTRAINT 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.


Scenario 2:

Creating a combination of columns as UNIQUE CONSTRAINT

Suppose we want to create a table named ‘employee’ in the database as shown below, with combination of ‘employee_id’ and ‘employee_name’ as the UNIQUE CONSTRAINT columns for the ‘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

We can achieve the same as:

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 UNIQUE (employee_id,employee_name)
);

Let’s insert some data in the newly created ‘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

Suppose we want to insert one more record with ‘employee_id = 105’ and ‘employee_name = Emp F’ then we can do this and oracle will not give any error because the combination of ‘employee_id’ and ‘employee_name’ is still unique.


Scenario 3:

Using ALTER TABLE statement to create UNIQUE CONSTRAINT on a table

As mentioned earlier that an ALTER TABLE statement can be used to create UNIQUE CONSTRAINT on a table.
The syntax to create UNIQUE CONSTRAINT using ALTER TABLE statement is:

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

Example:

ALTER TABLE employee
ADD CONSTRAINT employee_pk UNIQUE (employee_id);

Here we have altered the ‘employee’ table and have successfully created UNIQUE CONSTRAINT on ‘employee_id’ column.

We can also create UNIQUE CONSTRAINT by a combination of columns using the ALTER TABLE statement

Example:

ALTER TABLE employee
ADD CONSTRAINT employee_pk UNIQUE (employee_id, employee_name);

Here we have altered the ‘employee’ table and have successfully created UNIQUE CONSTRAINT on ‘employee_id’ and ‘employee_name’ column.


Filed Under: sql Tagged With: how to use unique constraint in oracle database query, how to use unique constraint in oracle plsql, how to use unique constraint in oracle sql, syntax and example of unique constraint in oracle database query, syntax and example of unique constraint in oracle plsql, syntax and example of unique constraint in oracle sql, unique constraint in oracle plsql, unique constraint in oracle sql, UNIQUECONSTRAINTPLSQL, using unique constraint in oracle database query, using unique constraint in oracle plsql, using unique constraint in oracle sql

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in