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.dev');
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.dev');
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.
π Use the Copy to mine
functionality to copy this snippet to your own personal collection and easy manage
your code snippets.
Codever is open source on Github βπ