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.

Answer 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

Answer 2

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?