Create a unique constraint on existing column in oracle database

Let’s say we have the following bookmark table and we want to add an unique constraint on the url column:

CREATE TABLE bookmark (
    id          NUMBER(10, 0),
    url         VARCHAR2(500 CHAR) ,
    PRIMARY KEY ( id )
);

INSERT INTO bookmark ( id, title, url, is_public) VALUES (1, 'https://www.codever.land');
INSERT INTO bookmark ( id, title, url, is_public) VALUES (2, 'https://www.codepedia.org');
INSERT INTO bookmark ( id, title, url, is_public) VALUES (3, 'https://www.codever.land');

Normally you would use the following ALTER TABLE command to add a unique constraint on the url column

ALTER TABLE bookmark ADD CONSTRAINT unique_url UNIQUE (url);

-- but you get an ORA-02299 saying the "unique_url" cannot be validated, because the table has duplicate key values

In this case and if you have existing data, that might not be unique you will get an ORA: error, if you execute the command before. However, you can overcome this if you use one of the following:

-- tell oracle not to validate the existing data at the time of the creation of the uniqueconstraint
ALTER TABLE bookmark ADD CONSTRAINT unique_url UNIQUE (url) DEFERRABLE NOVALIDATE;

-- create the unique constraint "disabled" and "enable" after that with the "NOVALIDATE" option
ALTER TABLE bookmark ADD CONSTRAINT unique_url UNIQUE (url) DISABLE;
ALTER TABLE bookmark ENABLE NOVALIDATE CONSTRAINT unique_url;

To see the created constraint and its attributes use the following command(s):

-- show all constraint for the table
SELECT * FROM user_constraints WHERE table_name = 'BOOKMARK'; -- note upper case

-- show our created unique constraint
SELECT * FROM user_constraints WHERE table_name = 'BOOKMARK'
   AND CONSTRAINT_NAME ='UNIQUE_URL';  -- note also here the upper case

To drop the unique constraint use the following command:

ALTER TABLE bookmark DROP CONSTRAINT unique_url;

Shared with from Codever.land. Use the Copy to mine functionality to copy this snippet to your own personal collection and easy manage your code snippets.

Subscribe to our newsletter for more code resources and news

Adrian Matei

Adrian Matei
Life force expressing itself as a coding capable human being