How to add default constraint with condition in Sql Server?

Question

I am using Sql Server 2014

CREATE TABLE [dbo].[Record](
    [RecordId] [int] IDENTITY(1,1) NOT NULL,
    [BookId] [int] NULL,
    [TopicId] [int] NULL,
    [BookName] [varchar](250) NULL,
    [TopicName] [varchar](250) NULL,
    [RecordPath] [varchar](250) NULL,
    [RecordName] [varchar](250) NULL,
    [DurationInSec] [float] NULL,
 CONSTRAINT [PK_Record] PRIMARY KEY CLUSTERED 
(
    [RecordId] ASC
)


ALTER TABLE dbo.Record ADD CONSTRAINT DC_RecordPath DEFAULT
             (CASE WHEN BookId = 1 THEN 'path/to/1' ELSE RecordPath END)

I want a default constraint with condition as above, but this is not permitted. Is there any workaround without using triggers?

Edit: Computed column is not working, because the column is filled by a few stored procedures. I have two conditions;

If BookId is 1 then RecordPath is path 1
Else for any BookId, RecordPath is RecordPath comes from stored procedure

@Lamak I added to question why computed column does not fit. — ibubi
– ibubi, Commented Nov 29, 2017 at 13:17
@Lamak actually doesn't work because computed columns doesn't create a constraint. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Nov 29, 2017 at 13:19
@JuanCarlosOropeza I know. I meant that this didn't sound like the constraint is needed at all, just a computed column — Lamak
– Lamak, Commented Nov 29, 2017 at 13:21
Do you want a DEFAULT value or a CONSTRAINT to make sure the value is always correct. If compute column doesnt work. You can use a TRIGGER — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Nov 29, 2017 at 13:23

Pred · Accepted Answer · 2017-11-29 13:46:08Z

0

This is what you need if you need that to check only the record with BookId = 1

CHECK (BookId = 1 AND RecordPath = 'path/to/1') OR (BookId != 1)

Esentialy this says: If BookId equals to 1, then the path has to be the one specified, or the BookId can be anything else and for those records the record path can be anything.

RecordPath = RecordPath is not necessary for the second part, since it is always TRUE

Or do you want a computed default?

That is not possible, DEFAULT constraint cannot depend on other columns. To achieve something similar, you'll need a TRIGGER or you can use a computed column or a view instead and use that column/view in your application when reading data.

edited Nov 29, 2017 at 13:46

answered Nov 29, 2017 at 13:39

Pred

9,0903 gold badges28 silver badges47 bronze badges

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

2 Comments

ibubi Over a year ago

Since check constraint can not assign value to the column which I need, I can not make use of that. What I'm looking was a computed & assignable/updatable column (can we say hybrid?) I guess I am going to use triggers.

Pred Over a year ago

If you have to solve it in one column, you'll have to use triggers. However if you just want a computed default value, a computed column can help if you write only the original column and read the computed one. Essentially the computed column is a simple COALESCE(RecordPath, CONCAT('path/to/', BookId)) or a view with an INSTEAD INSERT trigger on it.

Juan Carlos Oropeza · Accepted Answer · 2017-11-29 13:18:10Z

0

try this

ALTER TABLE dbo.Record ADD CONSTRAINT DC_RecordPath 
      CHECK ((CASE WHEN BookId = 1 THEN 'path/to/1' ELSE 'path/to/2' END)=RecordPath)

edited Nov 29, 2017 at 13:18

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

answered Nov 29, 2017 at 13:17

Dgan

10.3k1 gold badge33 silver badges51 bronze badges

1 Comment

ibubi Over a year ago

I have just tried, this doesn't fill the column RecordPath, it comes null

Collectives™ on Stack Overflow

How to add default constraint with condition in Sql Server?

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related