How to specify a conditional DEFAULT constraint in SQL Server?

Question

I am adding a column as a foreign key which cannot be NULL and so need to have a DEFAULT value.

ALTER TABLE location
ADD [retailer_brand_id] INT NOT NULL DEFAULT (SELECT retailer_id from retailer),
FOREIGN KEY(retailer_brand_id) REFERENCES retailer_brand(retailer_brand_id);

What I want to achieve is, get the retailer_id from SELECT retailer_id from retailer and if it is equal to 12 then set it to 0, otherwise set to the retailer_id returned by the select query.

When I use the above query, I get an error message

Subqueries are not allowed in this context. Only scalar expressions are allowed.

Do not use defaulting there, just let the column to be created will NULL's and UPDATE the column later. — miroxlav
– miroxlav, Commented Aug 1, 2018 at 23:39
SELECT retailer_id from retailer is likely going to give you more than one Id (unless there's only one entry in the table at the time). I'm not sure there's a way to dynamically assign a default value. — jonsca
– jonsca, Commented Aug 1, 2018 at 23:41

Ctznkane525 · Accepted Answer · 2018-08-01 23:41:33Z

1

I recommend a calculated column instead....so you don't also have to have this case statement in application logic as well as the table definition...don't want it in 2 spots...and don't have to worry about when retailerid changes...calc column would take care of that

answered Aug 1, 2018 at 23:41

Ctznkane525

7,5453 gold badges19 silver badges41 bronze badges

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

3 Comments

tRuEsAtM Over a year ago

okay, I haven't used calculated columns before, can you provide a sample for my query?

Ctznkane525 Over a year ago

which retailer are you pulling - SELECT retailer_id from retailer - most likely gives you one id...if you can resolve that piece...i can help you with the rest...there should be some type of link between your location data and what retailer you want?

tRuEsAtM Over a year ago

I have retailer specific database so every SELECT retailer_id from retailer fetches precisely one record, and yes I get the required id of the retailer from one of the columns.

Martin Průcha · Accepted Answer · 2021-02-28 17:32:37Z

0

I needed similar functionality. Calculated column is not an option for me, since my value should be changeble later by user, so I went with trigger on insert.

Described for example here: Trigger to update table column after insert?

answered Feb 28, 2021 at 17:32

Martin Průcha

3412 silver badges5 bronze badges

Collectives™ on Stack Overflow

How to specify a conditional DEFAULT constraint in SQL Server?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related