CODEWITHSUNDEEP
sqlsql-server
Wei Lin
Wei Lin

Reputation: 3811

No `trigger` to set column's default value from another column in the same table

Is it possible to use no trigger to set column's default value from another column in the same table?

Simple demo looks like:

DDL:

CREATE TABLE temp_T (col1 INT, col2 INT);  

ALTER TABLE temp_T  
    ADD CONSTRAINT col1_def DEFAULT col2 * 2 FOR col1; 

INSERT INTO temp_T (col2) VALUES (7);

I'm getting this error:

Msg 128 Level 15 State 1 Line 2
The name "col2" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

I expected these values:

col1   col2
------------
 14      7

Online TestDemo

Upvotes: 1

Views: 1681

Answers (3)

Hooman Bahreini
Hooman Bahreini

Reputation: 15569

You cannot use DEFAULT VALUE based on a different column. But then you may want to ask why you have such a requirement? If value of col2 is always going to be 2 * col1, then why do you want to store it in the DB and waste storage space?

If col2 is always 2 * col1, you can change your read query (or a stored procedure) to do that for you:

select col1, 2 * col1 as computed_col2 from temp_T

If you want a computed value, only when col2 is null, then you can do this:

select 
    col1, 
    case when col2 is null then col1 * 2 else col2 end as col2
from 
    temp_T

And if you you have some more complex logic you need to use a trigger.

Upvotes: 1

DavidG
DavidG

Reputation: 119056

Unfortunately, you can't use another column to calculate a default value for a table.

You can however create a computed column:

CREATE TABLE temp_T (col1 INT, col2 AS col1 * 2);

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ca13be5d4df4931bfe45ff1ee58768ff

Upvotes: 1

marc_s
marc_s

Reputation: 755053

You could create that column as a computed column instead of giving it a default:

CREATE TABLE temp_T (col1 INT, col2 AS col1 * 2 PERSISTED);

Now, if you insert the values:

INSERT INTO temp_T (col1) VALUES (7);

you do get 7 in col1 and 14 in col2.

But this is now a computed column, meaning you cannot ever insert some other value into it - it's always going to be twice the value in col1. Not sure if that would work in your scenario - but as the error says: you cannot use another column in a default expression for a column.

Upvotes: 3

Related Questions