Modify unique constraint in Oracle

Question

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?

Pileous

Answer 1

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

See: Oracle Docs

Duvetyn

The problem is that the constraint is referenced by some foreign keys. And so I can't drop it without changing them. – Pileous

But if you change the constraint, the combination of columns referenced by foreign keys will no longer be unique, so you will have to recreate them also. – Duvetyn

The two constraints should not be mutually exclusive. So create the new one, modify the foreign keys, then drop the old one. – Unsuccess

@Dave Costa Yes, if you need to preserve relationships during all times, then you would first create the new constraint and foreign keys and then drop the old ones. Then, if needed, you can rename new ones to old names. – Duvetyn

Oracle Docs link is broken. – Burrton

Oracle Docs link has been fixed. – Duvetyn