tags:

views:

593

answers:

1
+1  Q: 

Modify unique constraint in Oracle

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:

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

What's the problem with that?

+4  A: 

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

See: Oracle Docs

Majkel  2009-11-13 19:46:28
The problem is that the constraint is referenced by some foreign keys. And so I can't drop it without changing them.
wheleph  2009-11-13 19:49:54
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.
Majkel  2009-11-13 20:02:45
The two constraints should not be mutually exclusive. So create the new one, modify the foreign keys, then drop the old one.
Dave Costa  2009-11-13 20:08:52
@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.
Majkel  2009-11-13 20:16:10

related questions

What language do you use for Postgresql triggers and stored procedures?
Are Multiple DataContext classes ever appropriate?
Which tools do people use to create Data Dictionaries?
Any experiences with Protocol Buffers?
Mechanisms for tracking DB schema changes
How big can a MySQL database get before performance starts to degrade.
How do I index a database field
How does database indexing work?
How do I connect to a database and loop over a recordset in C#?
Editing database records by multiple users
Object Oriented vs Relational Databases
VFP .NET OLEdb provider does not work in Win 64-Bits. Help
Embedded Database for .net that can run off a network
Connect PHP to an AS/400
Swap unique indexed column values in database.
cx_Oracle - what is the best way to iterate over a result set?
cx_Oracle - How do I access Oracle from Python?
.NET Migrations Engine
Is there a version control system for database structure changes?
SQLite and XSD
How do I version my MS SQL database in SVN?
XSD DataSets and ignoring foreign keys
Flat File Databases in PHP
Throw Error In MySQL Trigger
Binary Data in MySQL