Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can you replace or update a SQL constraint?

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

like image 447
StormFoo Avatar asked May 14 '09 01:05

StormFoo


People also ask

How do I edit constraints in SQL Server?

The basic syntax of an ALTER TABLE command to add a NOT NULL constraint to a column in a table is as follows. ALTER TABLE table_name MODIFY column_name datatype NOT NULL; The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows.

Can we use Replace with update in SQL?

You can use REPLACE in an UPDATE statement.

Can you rename a constraint SQL?

You can use the sp_rename system stored procedure to rename a CHECK constraint in SQL Server. The purpose of this stored procedure is to allow you to rename user-created objects in the current database. So you can also use it to rename other objects such as tables, columns, alias data types, etc.


2 Answers

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.

like image 28
Scott Ferguson Avatar answered Oct 05 '22 16:10

Scott Ferguson


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.

like image 55
Scott Ivey Avatar answered Oct 05 '22 18:10

Scott Ivey