SQL Server modified CHECK constraint

Modify a Check constraint using the SSMS database management tool

1, open the database, select Data table-Right click-"SELECT Design (or expand the constraints, select the constraints, right-click, select Modify, the following steps are the same).

2. Select the data column to modify-Right click-select Check constraint.

3. In check Constraint popup-"SELECT constraint to modify-" input constraint expression-"input constraint name and constraint description-" Select Table Designer rule-"click Close.

4, click the Save button (or Ctrl+s)-"Refresh the table to see the results of the changes."

To modify a check constraint by using a T-SQL script

A CHECK constraint modification rule must first delete an existing CHECK constraint and then recreate it with the new definition in order to modify the constraint using Transact-SQL CHECK .

Grammar:

--Modify CHECK constraints
Use database name
Go

--If the constraint exists, delete it first
if exists (select * from sysobjects where name= constraint name)
ALTER TABLE name DROP CONSTRAINT constraint name;
Go

--Adding constraints
ALTER TABLE table name
--with check-whether the constraint applies to existing data, with check for existing data, with Nocheck to not apply to existing data
Add constraint constraint name
Check
Not FOR replication--disables the constraint when the replication agent inserts or updates data in the table.
(constraint expression);
Go

--Whether to disable the constraint when new data is added to the table or when existing data in the table is updated. Check indicates a checksum, nocheck means no checksum
--alter Table Name
--check
--constraint table name;
--go

--Add a Check constraint description
Execute sp_addextendedproperty N ' ms_description ', n ' constraint description ', n ' SCHEMA ', n ' dbo ', n ' table ', n ' tables name ', n ' CONSTRAINT ', n ' constraint name ';
Go

Example:

--Modify CHECK constraints
Use TESTSS
Go

--If the constraint exists, delete it first
if exists (select * from sysobjects where name= ' U_check2 ')
ALTER TABLE test1 drop constraint U_check2;
Go

--Adding constraints
ALTER TABLE Test1
--with check-whether the constraint applies to existing data, with check for existing data, with Nocheck to not apply to existing data
Add Constraint U_check2
Check
Not FOR replication--disables the constraint when the replication agent inserts or updates data in the table.
(height>=100 and Height <=200);
Go

--Whether to disable the constraint when new data is added to the table or when existing data in the table is updated. Check indicates a checksum, nocheck means no checksum
--alter table Test1
--check
--constraint U_check2;
--go

--Add a Check constraint description
Execute sp_addextendedproperty N ' ms_description ', n ' modify constraint ', n ' SCHEMA ', n ' dbo ', n ' TABLE ', n ' test1 ', n ' CONSTRAINT ', n ' u_ Check2 ';
Go

Check constraint modification pros and cons

Advantages:

1, modify the database check constraint can guarantee the normative and integrity of the data.

Disadvantages:

1: When you modify the constraints of the Table Designer when you use rules, you may be causing conflicts between the existing data and constraints.

SQL Server modified CHECK constraint