Add column to existent table with default costraint value based on condition

Question

Post reply

Add column to existent table with default costraint value based on condition

first100

SSC Enthusiast

Points: 174
More actions
February 16, 2021 at 10:12 pm

#3845865
Add column to existent table with default value based on condition
Hello,
i would to create a new column with default value, but i would know if its possible to speficy dfault value upon a value on another column,
example i would write something like:
```
ALTER TABLE [MyTable1] 
ADD NewRange bit NOT NULL default (IF MyTable1.Status <>'AAA' 1
ELSE 0);
GO
```
Or:
```
ALTER TABLE [MyTable1] 
ADD NewRange bit NOT NULL default (CASE WHEN MyTable1.Status <>'AAA' THEN 1 ELSE 0 END);
GO
```
but i dont now if its possible because the error that i have from sql is:
"is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted."
Is there any way to do this?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101117 More actions · Answer 1

No, not that way.

But, for this situation, you could make it a computed / virtual column.

ALTER TABLE [MyTable1]

ADD NewRange AS CAST(CASE WHEN Status <> 'AAA' /* OR Status IS NULL? */ THEN 1 ELSE 0 END AS bit);

GO

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 2

Are you just trying to set the initial value, when the column is created (i.e. and then DROP the DEFAULT and thereafter apply the value from programming?)

Or are you looking for a longer term DEFAULT which sets correctly for all new records (where a value for [Status] is not provided?)

Or do you want the [NewRange] BIT to ALWAYS represent whether [Status] equals/not-equals 'AAA'? (you could use a computed column for that ...)

I think I would tackle this with a default value which was the most common in the data, and then (i..e as part of the script that added the new column) change it for any rows that were the other value. The problem would be for any new rows (which did not provide a value for [Status] and thus needed the DEFAULT value).

You could use a Trigger to set the value, on new records, but the trigger wouldn't be able to know whether the value was set by DEFAULT or the Program ...

However, an INSTEAD OF trigger would be able to do that ... where the [Status] was NULL the trigger could then set the value as part of an INSERT statement (and, indeed, would have to do so because the column is defined as NOT NULL).

But I try to avoid INSTEAD OF TRIGGERS if possible because they have to be maintained when the table is modified - e.g. if you add a column in the future, and forget to modify the Instead Of Trigger, then the new column would not get saved at all 🙁

But maybe someone else has a better answer

ktflash Default port Points: 1400 More actions · Answer 3

would be nice to have something like this i guess, sucks it doesnt work

create table #table
(old_column int)

insert into #table
values(1),(2)

alter table #table
add new_column int default (case when old_column = 1 then 2 else 1 END) with values

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Steve Jones - SSC Editor SSC Guru Points: 736418 More actions · Answer 4

trigger makes this work, as Kristen noted, but it's complex in terms of maintenance and admin. Easy to lose track of these. However, if I needed to set a default, I'd use the trigger.

ScottPletcher SSC Guru Points: 101117 More actions · Answer 5

ktflash wrote:

would be nice to have something like this i guess, sucks it doesnt work

create table #table
(old_column int)

insert into #table
values(1),(2)

alter table #table
add new_column int default (case when old_column = 1 then 2 else 1 END) with values

You could do that also with a view. Naturally you can add any calc'd columns you want in a view. It's often a good idea to have all code use a view to the table rather than directly modify the actual physical table, since it can make table changes easier later.

CREATE VIEW dbo.table_name
AS
SELECT *, CASE WHEN ... THEN ... END AS new_column_name
FROM table_name_base

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 6

I'm thinking that the OP has left the building.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 7

ScottPletcher wrote:

It's often a good idea to have all code use a view to the table rather than directly modify the actual physical table, since it can make table changes easier later.

I needed you to tell me that 20 years ago!

I am curious what impact that would have on performance/query-plans, and how that would change code maintenance practice/procedure ...

I'll start a new thread Use a VIEW for all SELECTs on every table for futureproofing