カラム追加時のデフォルト値

• Oracle

(12cR1)

drop table tab1 purge;
create table tab1(col1 int);

insert into tab1 values(1);
commit;
select * from tab1;

alter table tab1 add (col2 int);
alter table tab1 add (col3 int not null);
→ORA-01758: 必須列(NOT NULL)を追加するには、表を空にする必要があります。

alter table tab1 add (col4 int default 123);
alter table tab1 add (col5 int default 123 not null);

select * from tab1;

COL1 COL2 COL4 COL5
---------- ---------- ---------- ----------
1 123 123

drop table tab1 purge;
create table tab1(col1 varchar2(10));

insert into tab1 values('A');
commit;
select * from tab1;

alter table tab1 add (col2 varchar2(10));
alter table tab1 add (col3 varchar2(10) not null);
→ORA-01758: 必須列(NOT NULL)を追加するには、表を空にする必要があります。

alter table tab1 add (col4 varchar2(10) default 'B');
alter table tab1 add (col5 varchar2(10) default 'B' not null);

select * from tab1;

COL1 COL2 COL4 COL5
---------- ---------- ---------- ----------
A B B

drop table tab1 purge;
create table tab1(col1 date);

alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';

insert into tab1 values(sysdate);
commit;
select * from tab1;

alter table tab1 add (col2 date);
alter table tab1 add (col3 date not null);
→ORA-01758: 必須列(NOT NULL)を追加するには、表を空にする必要があります。

alter table tab1 add (col4 date default sysdate);
alter table tab1 add (col5 date default sysdate not null);

select * from tab1;

COL1 COL2 COL4 COL5
------------------- ------------------- ------------------- -------------------
2019/12/08 06:51:32 2019/12/08 06:51:34 2019/12/08 06:51:34

• MySQL

(5.6)

drop table tab1;
create table tab1(col1 int);

insert into tab1 values(1);

select * from tab1;

alter table tab1 add (col2 int);
alter table tab1 add (col3 int not null);
→エラーとならない

alter table tab1 add (col4 int default 123);
alter table tab1 add (col5 int default 123 not null);

select * from tab1;

+------+------+------+------+------+
| col1 | col2 | col3 | col4 | col5 |
+------+------+------+------+------+
| 1 | NULL | 0 | 123 | 123 |
+------+------+------+------+------+

drop table tab1;
create table tab1(col1 varchar(10));

insert into tab1 values('A');

select * from tab1;

alter table tab1 add (col2 varchar(10));
alter table tab1 add (col3 varchar(10) not null);
→エラーとならない

alter table tab1 add (col4 varchar(10) default 'B');
alter table tab1 add (col5 varchar(10) default 'B' not null);

select * from tab1;

+------+------+------+------+------+
| col1 | col2 | col3 | col4 | col5 |
+------+------+------+------+------+
| A | NULL | | B | B |
+------+------+------+------+------+

drop table tab1;
create table tab1(col1 datetime);

insert into tab1 values(current_timestamp());

select * from tab1;

alter table tab1 add (col2 datetime);
alter table tab1 add (col3 datetime not null);
→エラーとならない

alter table tab1 add (col4 datetime default current_timestamp());
alter table tab1 add (col5 datetime default current_timestamp() not null);

select * from tab1;

+---------------------+------+---------------------+---------------------+---------------------+
| col1 | col2 | col3 | col4 | col5 |
+---------------------+------+---------------------+---------------------+---------------------+
| 2019-12-08 06:22:12 | NULL | 0000-00-00 00:00:00 | 2019-12-08 06:22:53 | 2019-12-08 06:22:54 |
+---------------------+------+---------------------+---------------------+---------------------+

• PostgreSQL

(9.4)

drop table tab1;
create table tab1(col1 int);

insert into tab1 values(1);

select * from tab1;

alter table tab1 add col2 int;
alter table tab1 add col3 int not null;
→ERROR: column "col3" contains null values

alter table tab1 add col4 int default 123;
alter table tab1 add col5 int default 123 not null;

\pset null 'NULL'
select * from tab1;

col1 | col2 | col4 | col5
------+------+------+------
1 | NULL | 123 | 123

drop table tab1;
create table tab1(col1 varchar(10));

insert into tab1 values('A');

select * from tab1;

alter table tab1 add col2 varchar(10);
alter table tab1 add col3 varchar(10) not null;
→ERROR: column "col3" contains null values

alter table tab1 add col4 varchar(10) default 'B';
alter table tab1 add col5 varchar(10) default 'B' not null;

\pset null 'NULL'
select * from tab1;

col1 | col2 | col4 | col5
------+------+------+------
A | NULL | B | B

drop table tab1;
create table tab1(col1 timestamp);

insert into tab1 values(clock_timestamp());

select * from tab1;

alter table tab1 add col2 timestamp;
alter table tab1 add col3 timestamp not null;
→ERROR: column "col3" contains null values

alter table tab1 add col4 timestamp default clock_timestamp();
alter table tab1 add col5 timestamp default clock_timestamp() not null;

\pset null 'NULL'
select * from tab1;

col1 | col2 | col4 | col5
----------------------------+------+----------------------------+---------------------------
2019-12-08 06:52:36.947499 | NULL | 2019-12-08 06:52:38.918247 | 2019-12-08 06:52:38.93145

• SQL Server

(2014)

drop table tab1;
create table tab1(col1 int);

insert into tab1 values(1);

select * from tab1;

alter table tab1 add col2 int;
alter table tab1 add col3 int not null;
→ALTER TABLE では、NULL を許可する列または DEFAULT 定義が指定されている列しか追加できません。
追加する列が ID 列またはタイムスタンプ列の場合、または前述の条件のいずれも満たされない場合は、
この列を追加できるようにテーブルは空である必要があります。
列 'col3' はこれらの条件を満たしていないため、空でないテーブル 'tab1' に追加できません。

alter table tab1 add col4 int default 123;
→デフォルト値はセットされない

alter table tab1 add col5 int default 123 not null;

select * from tab1;

col1 col2 col4 col5
----------- ----------- ----------- -----------
1 NULL NULL 123

drop table tab1;
create table tab1(col1 varchar(10));

insert into tab1 values('A');

select * from tab1;

alter table tab1 add col2 varchar(10);
alter table tab1 add col3 varchar(10) not null;
→ALTER TABLE では、NULL を許可する列または DEFAULT 定義が指定されている列しか追加できません。
追加する列が ID 列またはタイムスタンプ列の場合、または前述の条件のいずれも満たされない場合は、
この列を追加できるようにテーブルは空である必要があります。
列 'col3' はこれらの条件を満たしていないため、空でないテーブル 'tab1' に追加できません。

alter table tab1 add col4 varchar(10) default 'B';
→デフォルト値はセットされない

alter table tab1 add col5 varchar(10) default 'B' not null;

select * from tab1;

col1 col2 col4 col5
---------- ---------- ---------- ----------
A NULL NULL B

drop table tab1;
create table tab1(col1 datetime2);

insert into tab1 values(getdate());

select * from tab1;

alter table tab1 add col2 datetime2;
alter table tab1 add col3 datetime2 not null;
→ALTER TABLE では、NULL を許可する列または DEFAULT 定義が指定されている列しか追加できません。
追加する列が ID 列またはタイムスタンプ列の場合、または前述の条件のいずれも満たされない場合は、
この列を追加できるようにテーブルは空である必要があります。
列 'col3' はこれらの条件を満たしていないため、空でないテーブル 'tab1' に追加できません。

alter table tab1 add col4 datetime2 default getdate();
→デフォルト値はセットされない

alter table tab1 add col5 datetime2 default getdate() not null;

select * from tab1;

col1 col2 col4 col5
--------------------------- --------------------------- --------------------------- ---------------------------
2019-12-08 06:39:21.7200000 NULL NULL 2019-12-08 06:39:52.8230000