SQL column default value set using row wise operations

Tags: , , , ,



Is it possible to set a default value in a SQL column to be consisted of other columns? For example, in the table below is it possible to have the EndDate default to StartDate + NoOfDays? So when inserting, I just need to do INSERT INTO xxx VALUES StartDate NoOfDays.

enter image description here

Answer

You can use a generated column:

create table mytable (
    startdate datetime not null,
    noofdays int not null,
    enddate datetime as (startdate + interval noofdays day)
);

You would then insert as follows:

insert into mytable (startdate, noofdays) values(now(), 3);

Which generates:

startdate           | noofdays | enddate            
:------------------ | -------: | :------------------
2020-12-12 01:28:27 |        3 | 2020-12-15 01:28:27

Demo on DB Fiddle

Note that this not exactly the same thing as default, which you mentioned in the question. A default applies to regular column, and is used only when no value is provided at insert time. On the other hand, a generated column cannot be directly written to.


Edit

In SQL Server:

create table mytable (
    startdate datetime not null,
    noofdays int not null,
    enddate as dateadd(day, noofdays, startdate)
)

Demo



Source: stackoverflow