Learn the motive and how to unravel the ORA-01452 error message in Oracle.
When you come upon an ORA-01452 error, the following error message will appear:
ORA-01452: can’t CREATE UNIQUE INDEX; duplicate keys located
You tried to execute a CREATE UNIQUE INDEX announcement on one or greater columns that include duplicate values.
The option(s) to resolve this Oracle error are:
If the entries do not want to be unique, you can remove the UNIQUE keyword from your CREATE UNIQUE INDEX declaration and rerun the command.
If the entries want to be unique, delete all entries from the desk that create reproduction values. Then re-execute your CREATE UNIQUE INDEX statement.
For example, if you had a desk called suppliers described as follows:
CREATE TABLE suppliers ( supplier_name varchar2(50), city varchar2(35) );
Then executed the following INSERT statements:
INSERT INTO suppliers (supplier_name, city) VALUES ('IBM', 'New York'); INSERT INTO suppliers (supplier_name, city) VALUES ('IBM', 'Silicon Valley');
You then tried to create a unique index with the following statement:
CREATE UNIQUE INDEX supplier_idx ON suppliers (supplier_name);
You would receive the following error message:
You ought to correct this by means of developing your INDEX as a non-unique index.
CREATE INDEX supplier_idx ON suppliers (supplier_name);
OR you may want to dispose of one of the entries in the suppliers desk for IBM and then re-run your CREATE UNIQUE INDEX statement. For example:
DELETE FROM suppliers WHERE supplier_name = 'IBM' AND city = 'Silicon Valley'; CREATE UNIQUE INDEX supplier_idx ON suppliers (supplier_name);