SQL - Setting parameters for Default Column Value in Table

Question

Using SQL Server 2012.

I have a column in my member table that is created using the following script:

[EnableMemberWebAccess] [bit] NOT NULL DEFAULT ((1))

I want this column to only have 1 as a default if another column in this same table is a certain value. Let's call this column MemDesc, and I want the EnableMemberWebAccess to be defaulted to 1 when MemDesc = 'Founder', and for it to default to 0 when MemDesc != 'Founder'.

Any help much appreciated!

Answer 1

There is probably no way to achieve a default value that can be changed afterwards. Either you have a value that you insert in the beginning. You will then need to take care of consistency within the application:

ALTER TABLE *table* ADD COLUMN EnableMemberWebAccess bit NULL

UPDATE *table* SET *table.*EnableMemberWebAccess = CAST(CASE WHEN *table*.MemDesc = 'Founder' THEN 1 ELSE 0 END AS bit)

ALTER TABLE *table* ALTER COLUMN EnableMemberAccess bit NOT NULL

Or you have to use a computed column. This will not allow you to change the value of the column except if you change the value of the column it depends on.

Answer 2

Computed column should work for you:
ADD EnableMemberWebAccess AS cast((CASE WHEN MemDesc='Founder' THEN 1 ELSE 0 END) as bit)