Can you replace or update a SQL constraint?

sql constraints sql-update
StormPooper picture StormPooper · May 14, 2009 · Viewed 52.8k times · Source

I have written the following constraint for a column I've called 'grade':

CONSTRAINT gradeRule CHECK grade IN (‘easy’, ‘moderate’, ‘difficult’),

Is it possible to later update the gradeRule to have different values? For example, 'moderate' and 'difficult' could be changed to 'medium' and 'hard'.

Thanks

Answer

Scott Ivey picture Scott Ivey · May 14, 2009

You could drop the existing constraint, and add the new constraint with the NOCHECK option. This would allow you to add the constraint even though data in the table violates the constraint. The problem with doing this though would be that you wouldn't be able to update existing records without making them pass the constraint first.

ALTER TABLE SomeTable DROP CONSTRAINT gradeRule
GO
ALTER TABLE SomeTable ADD CONSTRAINT gradeRule ... WITH NOCHECK
GO

Although this is possible, its not usually recommended because of the potential problems with future updates of the data.

Related questions

Add primary key to existing table

I have an existing table called Persion. In this table I have 5 columns: persionId Pname PMid Pdescription Pamt When I created this table, I set PersionId and Pname as the primary key. I now want to include one more column …

Read More
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
Turn off constraints temporarily (MS SQL)

I'm looking for a way to temporarily turn off all DB's constraints (eg table relationships). I need to copy (using INSERTs) one DB's tables to another DB. I know I can achieve that by executing commands in proper order (to …

Read More