Wednesday , November 13 2019
Home / SQL / Set UNIQUE constraint by ALTER TABLE statement in Oracle

Set UNIQUE constraint by ALTER TABLE statement in Oracle

You can define UNIQUE constraint after creating table by ALTER TABLE statement.

Practice:

This practice will cover how to set UNIQUE constraint on an existing table column by ALTER TABLE statement.

Syntax:

ALTER   TABLE   table_name   ADD   CONSTRAINT uk_constraint_name  UNIQUE  (unique_column_name);

Here

unique_column_name is the name of table column name on which UNIQUE constraint will be set.

Example:

Step-1: Drop the table if already created.

DROP TABLE Recruitment;

Step-2: Create the table without UNIQUE constraint.

CREATE TABLE Recruitment

(

Applicant_Id         Varchar2(10),

Applicant_Name  Varchar2(50),

Post_Name           Varchar2(50),

Qualification        Varchar2(30),

Mobile_NO         Varchar2(15)

);

See the table structure.

DESCRIBE Recruitment;

Step-3:

Run the SELECT statement to confirm whether any data exists or not.

SELECT * FROM Recruitment;

Result of the SELECT statement.

no rows selected

That means that the table is empty.

Step-4: Now insert  two rows.

INSERT   INTO  Recruitment   VALUES  (‘1001′,’MD. ABDUL KARIM’,’ORACLE PROGRAMMER’,’Post Graduation’,’01953451041′);

INSERT  INTO  Recruitment  VALUES  (‘1002′,’MD. ABUL HOSSEN’,’ORACLE PROGRAMMER’, ‘Graduation’,’01957751045′);

Step-5:

Now query again to see the resultset..

SELECT * FROM Recruitment;

Result set of the SELECT statement:

APPLICANT_ID     APPLICANT_NAME    POST_NAME               QUALIFICATION              MOBILE_NO

1001           MD. ABDUL KARIM    ORACLE PROGRAMMER   Post Graduation                01953451041

1002           MD. ABUL HOSSEN    ORACLE PROGRAMMER   Graduation                        01957751045

There are two records in the table.

Step-6: Now try to set UNIQUE constraint on existing table by ALTER TABLE statement. The statement to be run:

ALTER TABLE Recruitment ADD CONSTRAINT uk_app_id UNIQUE (Applicant_Id);

In the above ALTER TABLE statement, the UNIQUE constraint named uk_app_id has been added with the APPLICANT_ID column of RECRUITMENT table that already exists.

This is how; we can add UNIQUE constraint with an existing table column.

Note: A UNIQUE constraint can be created on multiple columns. To do this, you have to run the following statement:

ALTER   TABLE   Recruitment   ADD   CONSTRAINT uk_app_id_post_name   UNIQUE  (Applicant_Id, Post_Name);

In the above ALTER TABLE statement, a UNIQUE constraint named uk_app_id_post_name has been added with two columns (APPLICANT_ID and POST_NAME) of RECRUITMENT table.

Now insert row by executing the following INSERT statement.

INSERT INTO Recruitment VALUES (‘1001′,’MD. ABDUL KARIM’,’PHP PROGRAMMER’,’Post Graduation’,’01953451041′);

Try to execute the above INSERT statement again without any change in the two column (APPLICANT_ID and POST_NAME) values where a UNIQUE constraint has been created. Then the system will generate the following error.

INSERT INTO Recruitment VALUES (‘1001′,’MD. ABDUL KARIM’,’PHP PROGRAMMER’,’Post Graduation’,’01953451041′)

Error at line 1

ORA-00001: unique constraint (PERVEJ.UK_APP_ID_POST_NAME) violated

Now change one value. Suppose we have changed the value of POST_NAME column. But the APPLICANT_ID column value (‘1001’) has still unchanged. Execute the following statement.

INSERT   INTO  Recruitment   VALUES  (‘1001′,’MD. ABDUL KARIM’,’ORACLE PROGRAMMER’,’Post Graduation’,’01953451041′);

The statement will be executed successfully.

So, if you create a UNIQUE constraint on two or more columns, then at least one column value of the constraint columns should be different.

Exercise:

1)Run the following statement to create INVOICE table.

CREATE  TABLE  INVOICE

(

Invoice_No           number,

Invoice_Date        date,

Customer_Id         number,

Customer_Name   varchar2(50)

);

Now add UNIQUE constraint on Invoice_No column of INVOICE table by ALTER  TABLE statement after creating the table.

2) Define UNIQUE constraint on existing table column.

3) Create a table named STORAGE by five  columns Storage_No, Storage_Date, Product_ID,Product_Name,Poduct_Type.

Then set UNIQUE constraint on Product_Id column of the table.

4) Which statement is used to define UNIQUE  constraint on already created table?

Check Also

Set NOT NULL constraint by ALTER TABLE statement in Oracle

You can set NOT NULL constraint on existing table column by ALTER TABLE statement. Practice: …

Set NOT NULL constraint by CREATE TABLE statement in Oracle

Definition of NOT NULL constraint: The NOT NULL constraint is used to restrict null values …

2 comments

  1. I see you don’t monetize your website, don’t waste your traffic, you can earn extra cash every month because you’ve got high quality content.
    If you want to know how to make extra bucks, search for:
    Mertiso’s tips best adsense alternative

  2. You completed certain nice points there. I did a search on the subject and found nearly all people will have the same opinion with your blog.

Leave a Reply

Your email address will not be published. Required fields are marked *