CODEWITHSUNDEEP
sqloracle-database
Prosenjit
Prosenjit

Reputation: 3

Even after dropping the unique constraint, I am getting ORA-00001: unique constraint violation

CREATE TABLE TEST (ID NUMBER(2) UNIQUE ,VAL VARCHAR2(100)
);

Table TEST created.

insert into   test values (1,2);
1 row inserted.

I tried to insert the below data again.

insert into   test values (1,2);
ORA-00001: unique constraint

So I altered the table and removed the unique constraint and tried

alter table test modify (id number(2))
Table TEST altered

insert into   test values (1,2);
ORA-00001: unique constraint

Upvotes: 0

Views: 535

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

The constraint is stored separately from the column definition -- even when defined using the shorthand you are using.

This db<>fiddle illustrates what is happening.

Use alter table drop constraint.

Giving constraints names is quite helpful. Otherwise, you have to search through system tables to find their name so they can be dropped.

Upvotes: 1

Related Questions