Sql-server – User defined table type with a check constraint

constraintsql-server-2012user-defined-type

I have a user defined table type where I want to make sure that a certain column is within a given range. This range however is defined in another table. So I thought I can use a check constraint on this column with a scalar valued function.

    CREATE TYPE [dbo].[Accounts] AS TABLE(
    [AccountName] [varchar](25) NOT NULL 
         CONSTRAINT CHK_AccountName CHECK (dbo.validateAccountName(AccountName) = 1),
    [Sales] [int] NOT NULL,
    [Returns] [int] NOT NULL
);

However I get a syntax error, when I do the same on a regular table it works. Based on MSFT documentation check constraints should be ok on table types. What am I missing here ? Any better approach to validate input on a table type ?

Best Answer

Although table types do allow unnamed CHECK constraints, it is not possible to use a user-defined function inside one.

The error message is:

Msg 2785 Level 16 State 1 Line 3
User-defined functions, user-defined aggregates, CLR types, and methods on CLR types are not allowed in expressions in this context.
Msg 1750 Level 16 State 0 Line 1
Could not create constraint. See previous errors.

See the db<>fiddle demo

Related Question