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

Question

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

marc_s · Accepted Answer · 2015-05-16 08:43:01Z

2

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!

answered May 16, 2015 at 8:43

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related