User defined table type with check constraint

Question

I want to create a table type for use in a stored procedure, but I want to ensure that one of the parameters passed into the table is one of two values.

If I'm interpreting the documentation correctly, the only column constraint available is primary key but please correct me if I'm wrong.

Here's what I've tried:

create type dealerDisable as table
(id int not null,
dealerNo varchar(10) not null,
dealerName varchar(50),
disabling varchar(5),
constraint ratesOrAll 
check (disabling in ('Rates','All')),
dateAdded date)

Here's the error I get, which isn't all that illuminating:

Answer 1

The syntax for creating table types is a little more restrictive - it does not support named constraints, along with some other differences compared to CREATE TABLE.

CREATE TYPE dbo.dealerDisable AS TABLE -- ALWAYS use schema!
(
  id int not null,
  dealerNo varchar(10) not null,
  dealerName varchar(50),
  disabling varchar(5) check (disabling in ('Rates','All')),
  dateAdded date
);

When you try to insert a value, you can see that the system had to create a unique constraint name for each instance of the table:

DECLARE @d dbo.dealerDisable2;
INSERT @d VALUES(1,'blat','foo','None',getdate());

DECLARE @e dbo.dealerDisable2;
INSERT @e VALUES(1,'blat','foo','None',getdate());

Errors:

Msg 547, Level 16, State 0
The INSERT statement conflicted with the CHECK constraint "CK__#BCAA5D0E__disab__BD9E8147".
...

Msg 547, Level 16, State 0
The INSERT statement conflicted with the CHECK constraint "CK__#AAF87C65__disab__ABECA09E".
...

If you think about the mechanism behind this, it makes sense. For some types of constraints, you can only have one in the database with the same name. This would yield the same type of error, for example:

CREATE TABLE #a(id int, CONSTRAINT PK1 PRIMARY KEY (id));

CREATE TABLE #b(id int, CONSTRAINT PK1 PRIMARY KEY (id));

Now, while it's true that you can have as many check constraints with the same name as you like, this is probably just a blunt simplification of the syntax.

Answer 2

Please refer to Documentation. The correct syntax should be as below. Without the CONSTRAINT keyword

create type dealerDisable as table
(
    id int not null,
    dealerNo varchar(10) not null,
    dealerName varchar(50),
    disabling varchar(5),
        check (disabling in ('Rates','All')),
    dateAdded date
)