Modify unique constraint in Oracle

sql oracle constraints ora-00933
wheleph picture wheleph · Nov 13, 2009 · Viewed 37.5k times · Source

I need to update an existing constraint in Oracle database to add a new column there.

ALTER TABLE MY_PARTNER_DETAILS 
MODIFY CONSTRAINT UQ_MY_PARTNER_DETAILS
UNIQUE(PARTNER_CODE,PGOOD_CODE,SITE_CODE,PARTNER_PLACEMENT,PARTNER_PARTICIPATION)

Gives the error:

Error at line 1
ORA-00933: SQL command not properly ended

What's the problem with that?

Answer

Majkel picture Majkel · Nov 13, 2009

You should drop and recreate the constraint. modify constraint allows you to change constraint's state not definition.

See: Oracle Docs

Related questions

Display names of all constraints for a table in Oracle SQL

I have defined a name for each of the constraint for the multiple tables that I have created in Oracle SQL. The problem is that to drop a constraint for the column of a particular table I need to know …

Read More
Oracle find a constraint

I have a constraint called users.SYS_C00381400. How do I find what that constraint is? Is there a way to query all constraints?

Read More
How to give a unique constraint to a combination of columns in Oracle?

I have a Table with 4 Columns Each Column will be A,B,C,D Column A is the Primary key. Column B has unique name constraint. Now I want to remove the unique constraint for column B and give a …

Read More