Monday , September 23 2019
Home / SQL / Set UNIQUE constraint ignoring duplicate data in Oracle

Set UNIQUE constraint ignoring duplicate data in Oracle

When we create constraints in oracle, by default, the constraint exists in NON DEFERRABLE and VALIDATE state. So, by default, oracle checks each time for unique data when we insert or update data.

You can create constraint as DEFERRABLE and NOVALIDATE state. In the situation, oracle will not check existing duplicate data. Duplicate data will be checked for future new and modified rows. You can create constraint as DEFERRABLE and NOVALIDATE by the following clauses.

DEFERRABLE INITIALLY IMMEDIATE NOVALIDATE;

Practice:

This practice will cover how to set UNIQUE constraint on an existing table column ignoring duplicate data.

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)

);

Step-3: Now insert the rows.

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

Insert the same record again whose APPLICANT_ID is ‘1001’.

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

Step-4:

Now query to see the result set..

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

1001           MD. ABDUL KARIM    ORACLE PROGRAMMER   Post Graduation     01953451041

From the result above we see that the record of APPLICANT_ID    ‘1001’ has been inserted twice. The record of APPLICANT_ID    ‘1001’ is now in duplicate records due to not have unique integrity in the column APPLICANT_ID.

Step-5: The step-5 is so much important. Remember strongly that duplicate record already exists in the table. Now try to set UNIQUE constraint on existing table which containing currently duplicates records. The statement to be run:

ALTER TABLE Recruitment ADD CONSTRAINT uk_app_id UNIQUE (Applicant_Id);

When you execute the above ALTER statement the following error (duplicate keys found) is displayed. Because you are trying to set UNIQUE constraint on the table where duplicate values already exist.

ALTER TABLE Recruitment ADD CONSTRAINT uk_app_id UNIQUE (Applicant_Id)

Error at line 1

ORA-02299: cannot validate (PERVEJ.UK_APP_ID) – duplicate keys found

Now first you have to delete duplicate rows. Then run the ALTER statement. But it is time consuming and expensive. So, you can run the following ALTER statement which ignores existing duplicate data.

ALTER TABLE Recruitment ADD CONSTRAINT uk_app_id_dpl UNIQUE (Applicant_Id) DEFERRABLE INITIALLY IMMEDIATE NOVALIDATE;

In the above ALTER TABLE statement, a UNIQUE constraint named uk_app_id_dpl has been created on APPLICANT_ID column of RECRUITMENT table. But, here extra four keywords (DEFERRABLE INITIALLY IMMEDIATE NOVALIDATE) have been added in the end of ALTER TABLE statement. It will ignore existing duplicate data. So, the future inserted or updated data will be checked for uniqueness.

This is how; we can create constraint ignoring existing duplicate rows.

Exercise:

1) Create a table which name is COURSES by three columns. The columns are Course_No, Course_Name, Duration.

Then insert two same course no suppose 101 and 101 (duplicate rows) in the Course_No column of COURSE table.

Like,

Course_No  Course_Name  Duration

—————  ——————  ————-

101             MCSE                12 months

101             ORACLE            10 months

Now set the UNIQUE constraint on Course_No column by ALTER TABLE statement.

2) Which keywords are used with ALTER TABLE statement to define UNIQUE constraint ignoring duplicate rows?

3) Why (DEFERRABLE INITIALLY IMMEDIATE NOVALIDATE) keywords are used to?

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 …

One comment

  1. Respect to website author, some superb selective information.

Leave a Reply

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