Creating Check Constraints using Transact-SQL
When you are creating a new table, you can include check constraints in the CREATE TABLE command. There are several variations upon the syntax and positioning of the check constraint that can be used, depending upon the results that you require. The syntax that we will use for the check constraint itself is:
CONSTRAINT constraint-name CHECK (predicate)
This syntax allows us to name the constraint and to provide the predicate. It is possible to omit the first two parts of this syntax, leaving only the CHECK keyword and the expression. However, if you do so SQL Server will automatically generate a name for the constraint, which will make future maintenance more difficult.
Check constraints can be defined at the column level. In this case, the constraint is added to the end of the column definition in the CREATE TABLE command. The expression in a column-level check constraint may not reference any other column in the table. In the case of the previously defined constraint this is acceptable, so the table could have been created using the following T-SQL statement:
CREATE TABLE Contracts
(
ContractNumber INT IDENTITY NOT NULL,
CustomerAddressId INT NOT NULL,
RenewalDate SMALLDATETIME,
RenewAutomatically BIT,
ContractValue MONEY NOT NULL
CONSTRAINT CK_Contracts_ValueIsPositive CHECK (ContractValue >= 0),
DaysUntilRenewal AS datediff(d,getdate(),RenewalDate)
CONSTRAINT PK_Contracts PRIMARY KEY CLUSTERED
(
ContractNumber
)
)You may also connect a check constraint to an entire table, rather than a single column. When creating a table-level constraint, the expression can include references to any column, allowing comparison of several column values as required. To create this type of constraint, it is added to the list of columns and constraints for the table, separated from other elements with a comma. We could define the Contracts table using a table-level constraint as follows:
CREATE TABLE Contracts
(
ContractNumber INT IDENTITY NOT NULL,
CustomerAddressId INT NOT NULL,
RenewalDate SMALLDATETIME,
RenewAutomatically BIT,
ContractValue MONEY NOT NULL,
DaysUntilRenewal AS datediff(d,getdate(),RenewalDate)
CONSTRAINT PK_Contracts PRIMARY KEY CLUSTERED
(
ContractNumber
),
CONSTRAINT CK_Contracts_ValueIsPositive CHECK (ContractValue >= 0)
)Adding a Check Constraint to an Existing Table
Often you will want to add a constraint to an existing table. As with other constraint types, this can be achieved using the ALTER TABLE command's ADD clause.
We can use this method to add a new constraint to the Engineers table. In this case, we will ensure valid input of the engineer's charges by adding a check that the hourly rate is less than or equal to the overtime rate in all cases.
ALTER TABLE Engineers
ADD CONSTRAINT CK_Engineers_OvertimeRateValid
CHECK (HourlyRate <= OvertimeRate)
Try executing the following INSERT statement. The check constraint should prevent this change from being accepted.
INSERT INTO Engineers
(EngineerName, HourlyRate, OvertimeRate)
VALUES
('Bob Smith', 20, 19.50)Ignoring Existing Invalid Data
When creating constraints, it is possible that data already exists that does not meet the criteria enforced by the constraint's predicate. If this is the case, the creation of the check constraint will fail. As mentioned previously, it is possible to add a constraint without checking the existing data in the table. In this case, any data that does not pass the constraint's checks will remain in the table but new inserts and updates will be restricted as expected. This allows you to create the constraint and request that the database's users repair the invalid information at a a later time.
To create a constraint and ignore invalid information, add the WITH NOCHECK clause to the ALTER TABLE command as follows:
ALTER TABLE Engineers WITH NOCHECK
ADD CONSTRAINT CK_Engineers_OvertimeRateValid
CHECK (HourlyRate <= OvertimeRate)
Creating a Check Constraint that is Ignored During Replication
As with creating a check constraint using the SSMS graphical user interface, you can generate constraints using T-SQL that are not tested against information that is created or modified during replication. To do so, the "NOT FOR REPLICATION" clause is appended to the CHECK keyword:
ALTER TABLE Engineers
ADD CONSTRAINT CK_Engineers_OvertimeRateValid
CHECK NOT FOR REPLICATION (HourlyRate <= OvertimeRate)
Dropping Check Constraints
If you no longer require a check constraint because the business rules governing the database have changed, you can drop it using the standard syntax for removing a constraint. You should only drop a constraint if it will definitely not be needed again. Otherwise, you may wish to consider simply disabling it. To drop the constraint on the Engineers table, you could use the following T-SQL:
ALTER TABLE Engineers DROP CONSTRAINT CK_Engineers_OvertimeRateValid
Disabling Check Constraints
There are occasions when you may want to temporarily disable a check constraint. This is simple to achieve with the ALTER TABLE command and the NOCHECK clause. In the example below, the CK_Engineers_OvertimeRateValid check constraint is disabled:
ALTER TABLE Engineers NOCHECK CONSTRAINT CK_Engineers_OvertimeRateValid
Re-Enabling a Disabled Constraint
When you have completed the process that required the constraint to be disabled, you can re-enable it using the CHECK clause instead of NOCHECK:
ALTER TABLE Engineers CHECK CONSTRAINT CK_Engineers_OvertimeRateValid
By default, when you re-enable a check constraint, the data in the table is not retested. Any information that breaks the business rule remains unchanged. Sometimes you will prefer that the table's data is rechecked and that the check constraint is not re-enabled if invalid information exists. If this is the case, add the WITH CHECK clause to the ALTER TABLE command, as follows:
ALTER TABLE Engineers WITH CHECK
CHECK CONSTRAINT CK_Engineers_OvertimeRateValid
20 April 2009