Search code examples
t-sqluser-defined-types

Error while defining constraints in user defined table in t-sql


I am trying to create a user defined table type where I want to define a foreign key and unique key constraints but I'm unable to do so. Whenever I run the following I got the error "Incorrect syntax near the keyword 'CONSTRAINT'." Please someone tell me what's wrong with the code?

create Type firstType as Table
(
    firstId int IDENTITY (1,1)CONSTRAINT PK_firstId PRIMARY KEY CLUSTERED (firstId),
    f_email varchar(200) CONSTRAINT AK_email UNIQUE(f_email) ,
    fname varchar(100),
    outId int CONSTRAINT FK_First FOREIGN KEY (outId) REFERENCES New(newId)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)

Solution

  • A table type cannot have named constraints and cannot have foreign keys. It may have only unnamed primary keys, unique keys and check constraints. See https://learn.microsoft.com/en-us/sql/t-sql/statements/create-type-transact-sql?view=sql-server-ver15 for details.

    You can use this instead:

    CREATE Type firstType as Table
    (
        firstId int IDENTITY (1,1) PRIMARY KEY CLUSTERED (firstId),
        f_email varchar(200) UNIQUE(f_email) ,
        fname varchar(100),
        outId int
    )