Reputation: 3678
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.
Upvotes: 1
Views: 1906
Reputation: 341
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?
Upvotes: 0
Reputation: 7465
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
Upvotes: 1