User Tools

Site Tools


notes:sql:check

CHECK constraint

Define CHECK constraints using CREATE TABLE:

IF OBJECT_ID('Items') IS NOT NULL DROP TABLE Items
GO
CREATE TABLE Items
(
    -- A column with a check constraint
    STATUS INT NOT NULL DEFAULT 0 CONSTRAINT CK_Items_Status CHECK (STATUS IN (0, 1, 2)),
 
    -- Another column with a check contraint
    BirthDate DATETIME NULL CHECK (BirthDate < GETDATE())
)
GO

Add and disable a CHECK constraint:

-- Add a check constraint on the column Status
ALTER TABLE Items WITH CHECK ADD CONSTRAINT CK_Items_Status CHECK (STATUS IN (0, 1, 2))
 
-- Disable the check constraint
ALTER TABLE Items NOCHECK CONSTRAINT CK_Items_Status

Re-create a CHECK constraint:

ALTER TABLE Customer DROP CONSTRAINT CK_CustomerType
GO
ALTER TABLE Customer WITH CHECK ADD CONSTRAINT CK_CustomerType CHECK (CustomerType IN (0, 1, 2, 3))
GO
notes/sql/check.txt · Last modified: 2015/06/24 by admin