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
)
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
)