Reusing CHECK CONSTRAINTS

Question

Hello,

In SQL Server assuming one has columns that need to have the same data type is it possible to define a check constraint at the table (or database level) and apply that to a column when you define it?

As in this (contrived) example:

ALTER TABLE dbo.tblAuditTrail
ADD CONSTRAINT CK_DecimalNumber
CHECK (DecimalColumn LIKE '^\-?\d+\.\d+$')
GO

How now can you associate that with one or more columns having created it at the table level or is the answer to this to use a RULE viz.

CREATE RULE RU_Decimal
AS
@value LIKE '^\-?\d+\.\d+$'
GO

I know that the example is contrived and one would use a decimal column for decimal values but assume, because of a poor design choice, that this was an nchar column and you wanted to enforce some constraints on it.

Answer 1

Although rules do meet your requirements, they are now deprecated in favour of plain old check constraints. It wouldn't be a good idea to use rules if you think the database may need to be moved to future versions of SQL Server. See the "important" message at the top of the MSDN documentation: http://msdn.microsoft.com/en-us/library/ms188064.aspx.

You can get some reuse by using a user defined function and call it in all the check constraints but you will still need to explicitly define the check constraints on all columns where it applies.

CREATE FUNCTION IsDecimal(@input varchar(max))
RETURNS bit
AS 
BEGIN
    IF @value LIKE '^\-?\d+\.\d+$'
        RETURN 1
    ELSE
        RETURN 0
END
GO 

ALTER TABLE dbo.tblAuditTrail     
    ADD CONSTRAINT CK_DecimalNumber     
    CHECK (dbo.IsDecimal(DecimalColumn) = 1)     
GO

Answer 2

Your constraint is a type constraint.

With SQL, you can deal with this situation through user-defined types, where your "reuse" is materialised by declaring multiple columns to be of this type,

or you must repeat the type constraint as a CHECK constraint for each individual column.