Create a type with default value and check constraint in SQL Server 2005

Question

What's the recommended way to create a type in MSSQL 2005 with a default value and a check constraint to be used in several columns?

I don't want to add a check constraint and a default value for every single column.

My first idea was to create a default and a rule to bind to this type but books online clearly states that we should avoid using CREATE DEFAULT and CREATE RULE for new development because these commands will be removed in a future version.

Answer 1

Use a check constraint

example

create table bla (value varchar(50) 
default 'A' 
constraint CheckValue check (len(value) > 0))
go
insert bla default values
go
insert bla values('1')
go
insert bla values('') --will fail
go

Answer 2

Maybe you can accomplish it with a User Defined Data Type but even then you will have to change the data type for all your columns

Answer 3

I use CREATE TYPE and CREATE RULE to set default constraints, but not a default value; that's still done at the table level, but there's no good reason why you can't. I also like to use named table-level constraints instead of relying on the system-generated names for the column-level syntax.

I use this template script for my snippet engine; the "~~" are replacement parameter delimiters:

IF NOT EXISTS
(
    SELECT
        *
    FROM
        INFORMATION_SCHEMA.DOMAINS d
    WHERE
        (d.DOMAIN_CATALOG = DB_NAME()) AND
        (d.DOMAIN_SCHEMA = N'~~Schema.Name~~') AND
        (d.DOMAIN_NAME = N'Type~~TypeAndRule.Name~~')
)
BEGIN
    PRINT N'  ~~Schema.Name~~.Type~~TypeAndRule.Name~~, ~~Schema.Name~~.Rule~~TypeAndRule.Name~~';

    EXEC sp_executesql @statement = N'CREATE TYPE ~~Schema.Name~~.Type~~TypeAndRule.Name~~ FROM ~~baseTypeDef~~ NOT NULL;';
    EXEC sp_addextendedproperty N'MS_Description', N'Encapsulates XXX.', N'SCHEMA', N'~~Schema.Name~~', N'TYPE', N'Type~~TypeAndRule.Name~~';

    EXEC sp_executesql @statement = N'CREATE RULE ~~Schema.Name~~.Rule~~TypeAndRule.Name~~ AS (@value ~~atValueComparison~~);';
    EXEC sp_addextendedproperty N'MS_Description', N'Enforces XXX.', N'SCHEMA', N'~~Schema.Name~~', N'RULE', N'Rule~~TypeAndRule.Name~~';

    EXEC sp_bindrule @rulename = N'~~Schema.Name~~.Rule~~TypeAndRule.Name~~', @objname = N'~~Schema.Name~~.Type~~TypeAndRule.Name~~';
END;
GO

1. TypeAndRule.Name is the base name.
2. Schema.Name is the schema; e.g. dbo.
3. baseTypeDef is the underlying type definition; e.g. int, or nvarchar(50).
4. atValueComparison is the constraint expression.
5. The IF NOT EXISTS condition allows the script to run multiple times without errors.

The CREATE TYPE/RULE statements were originally deprecated in the SQL 2005 time frame and are still around. I doubt that they'll cause script errors any time before 2015 since MS have to maintain backward compatibility :-)

Answer 4

Yes, fine. Mercifully it is still all there is SQL Server 2008. Robyn Page's article on Simple-Talk explains it all

Robyn Page's SQL Server Data Validation Workbench

(I have a feeling I helped her a bit with that one) Just create a Type, and bind defaults and rules to it.

The deprecation issue is a bit of a shame. All that has happened is that rules, types and defaults have fallen foul of the SQL Standard. The preferred way now is to use constraint to do the same thing, but it is a lot less elegant if you need to do it uniformly in several tables. Also, it is only people like Joe Celko who understand constraints in their entirety. Sure as anything I don't.

Answer 5

I'm using SQL CLR integration, with the idea that we store data with general types like (char, int), but then contextualize the data with constraints, specifically 'check' constraints.

Create a data type in a visual studio database project (C#) which internally stores the value as a primitive type (e.g. int, float, string, etc.), but constrains the values it can be constructed with. For example, if you had a Score type, the constructor would throw an error if the value is outside the range of 0 to 100, ensuring that only valid Score objects can exist.

Your check constraint in the database table would simply involve calling a CLR function (which you would also create in your database project) that just tries to construct an instance of your C# data type with the new column value that you pass to it. The function succeeds if the value is constructed, and fails if the constructor throws an error.

You then have a constrained data type, centralized in your C# code, usable throughout your application, which also enforces values in the database.