CODEWITHSUNDEEP
sql-server
Happy .NET
Happy .NET

Reputation: 521

How to add default constraint on an existing column in SQL Server

I am creating a table that has default constraint for a column that has default value is other column value

This is my table script:

CREATE TABLE [ADMIN].[TblUserType]
(
    [FunctionId] [tinyint] NOT NULL,
    [UserTpyeId] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](50) NOT NULL,
    [ParentId] [int] NOT NULL,
    [CreatedBy] [int] NOT NULL,
    [UpdatedBy] [int] NOT NULL,
    [CreatedOn] [datetime] NOT NULL,
    [UpdatedOn] [datetime] NOT NULL,
    [Status] [char](1) NOT NULL
)

Now I am altering table using this script 

ALTER TABLE ADMIN.TblUserType
ADD CONSTRAINT De_Value DEFAULT UserTpyeId FOR (ParentId)

I am getting error like this

Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

But I need to add that column (UserTpyeId) value has default value for this column (ParentId)

How to do this?

Thank you

Upvotes: 3

Views: 976

Answers (1)

marc_s
marc_s

Reputation: 754268

You cannot use another column in a default constraint for a table - you'll need to use an AFTER INSERT trigger to achieve this:

CREATE TRIGGER trg_tblUserType_Insert
ON ADMIN.TblUserType
AFTER INSERT 
AS 
BEGIN
    UPDATE ut
    SET ut.ParentId = ut.UserTpyeId
    FROM ADMIN.TblUserType ut
    INNER JOIN Inserted i ON ut.UserTpyeId = i.UserTpyeId
END

Also: you have a typo in UserTpyeId - if ever possible, fix this before going live!

Upvotes: 2

Related Questions