30

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

Juan Mellado
  • 14,973
  • 5
  • 47
  • 54
StormFoo
  • 1,129
  • 2
  • 10
  • 25

3 Answers3

33

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.

Scott Ivey
  • 40,768
  • 21
  • 80
  • 118
  • Just to clarify: you recommend dropping the existing constraint then adding the revised constraint *without* the NOCHECK option, right? – onedaywhen May 14 '09 at 08:02
  • Yeah, as mentioned in other answers, I can drop the constraint, update the data, THEN make the new constraint. I would use NOCHECK if I wanted to add the constraint THEN update the data, but it can cause problems. – StormFoo May 14 '09 at 12:12
  • The correct answer is on http://stackoverflow.com/questions/13244889/how-to-alter-constraint -- No, you cannot alter constraints ever. – Quigi Jan 14 '16 at 23:16
6

Drop the constraint, and then add the replacement constraint. You can't update a constraint in SQL Server at least.

ALTER TABLE SomeTable DROP CONSTRAINT gradeRule

In addition, you'll need to update the table data before adding the new constraint, so that it meets the new constraint.

Scott Ferguson
  • 7,690
  • 7
  • 41
  • 64
5

If you change the constraint, all of the data currently in the table must meet the constraint. So if you had 2 rows of data with 'moderate' and tried to change the constraint to easy, medium, and hard, it would not let you.

So you would have to make the new constraint (easy, moderate, medium, difficult, hard) or, update the data to the new values - moderate --> medium etc.

Mark Sherretta
  • 10,160
  • 4
  • 37
  • 42
  • The original question was whether it is possible to change the constraint. Scott Ivey's answer, though not direct, seems to be in the negative (no dice; gotta DROP it). Yours, on the contrary seems to be in the affirmative. But with what SQL statement do you "change the constraint"? – Quigi Jan 14 '16 at 23:12