データが存在するテーブルに not null 制約の付いたカラムが追加できる
not null に関しておかしな話を聞いたので調べてみた。
おかしな話の内容
「データが存在するテーブルにnot null制約の付いたカラムが追加できる」
…うそん！！！
前置き
発言者はmysqlの事を言っていると思う。
なぜなら、
発言者はmysqlを使っている製品に「今」関わって（昔は知らない）いて、
私の経験として、「データが存在するテーブルにnot null制約の付いたカラムが追加できる」
なんて無茶はoracle、postgresでは許された事がない。
あ、忘れてた、SQLServerも許してくれなかった。
調査
他の RDBMS の挙動は分かっているので、mysql に対してのみ確認します。
データが存在するテーブルへのnot null制約の付いたカラム追加
テーブルを作成
CREATE TABLE test_null( null_char_column varchar(10), null_int_column int, notnull_char_column varchar(10) NOT NULL, notnull_int_column int NOT NULL );
定義を確認
desc test_null; +---------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------------+-------------+------+-----+---------+-------+ | null_char_column | varchar(10) | YES | | NULL | | | null_int_column | int(11) | YES | | NULL | | | notnull_char_column | varchar(10) | NO | | NULL | | | notnull_int_column | int(11) | NO | | NULL | | +---------------------+-------------+------+-----+---------+-------+
notnull_char_column、notnull_int_column の2カラム。
not null 指定したから Null のところが NO となっているのは正しく、
Default が NULL なのもOK。
データを INSERT する時に指定してなかったらNULLなのでエラー、というのは
至極全うな動きだ。
データ追加
INSERT INTO test_null (null_char_column, null_int_column, notnull_char_column, notnull_int_column) VALUES('a', 1, 'b', 2);
データを確認
select * from test_null; +------------------+-----------------+---------------------+--------------------+ | null_char_column | null_int_column | notnull_char_column | notnull_int_column | +------------------+-----------------+---------------------+--------------------+ | a | 1 | b | 2 | +------------------+-----------------+---------------------+--------------------+
not null 制約の付いたカラムを追加する。
ALTER TABLE test_null ADD notnull_char_column_add varchar(10) not null; ALTER TABLE test_null ADD notnull_char_int_add int not null;
がーん、、、エラーにならなかった･･･
定義を確認
desc test_null; +-------------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------------+-------------+------+-----+---------+-------+ | null_char_column | varchar(10) | YES | | NULL | | | null_int_column | int(11) | YES | | NULL | | | notnull_char_column | varchar(10) | NO | | NULL | | | notnull_int_column | int(11) | NO | | NULL | | | notnull_char_column_add | varchar(10) | NO | | NULL | | | notnull_char_int_add | int(11) | NO | | NULL | | +-------------------------+-------------+------+-----+---------+-------+
CREATE TABLE の時に作成した他の not null 制約付きカラムと同じ定義で追加されてる。
データを確認
select * from test_null; +------------------+-----------------+---------------------+--------------------+-------------------------+-----------------+ | null_char_column | null_int_column | notnull_char_column | notnull_int_column | notnull_char_column_add | notnull_int_add | +------------------+-----------------+---------------------+--------------------+-------------------------+-----------------+ | a | 1 | b | 2 | | 0 | +------------------+-----------------+---------------------+--------------------+-------------------------+-----------------+
追加した int 型カラムのレコードに 0 が勝手に入ってる･･･。
varchar 型カラムの方は多分''かな？
確認してみる。
select * from test_null where notnull_char_column_add is null; Empty set (0.00 sec) select * from test_null where notnull_char_column_add is not null; +------------------+-----------------+---------------------+--------------------+-------------------------+-----------------+ | null_char_column | null_int_column | notnull_char_column | notnull_int_column | notnull_char_column_add | notnull_int_add | +------------------+-----------------+---------------------+--------------------+-------------------------+-----------------+ | a | 1 | b | 2 | | 0 | +------------------+-----------------+---------------------+--------------------+-------------------------+-----------------+ 1 row in set (0.00 sec) select * from test_null where notnull_char_column_add = ''; +------------------+-----------------+---------------------+--------------------+-------------------------+-----------------+ | null_char_column | null_int_column | notnull_char_column | notnull_int_column | notnull_char_column_add | notnull_int_add | +------------------+-----------------+---------------------+--------------------+-------------------------+-----------------+ | a | 1 | b | 2 | | 0 | +------------------+-----------------+---------------------+--------------------+-------------------------+-----------------+ 1 row in set (0.00 sec)
という事でやっぱり varchar 型の（勝手な）初期化に使われる値は''でした。
INSERT で not null のカラムにデータを指定しないとどうなるのか確認してみる
答えは予想がつきますが、予想外の仕様を↑で見たばかりなので念のため。
データ追加
INSERT INTO test_null (null_char_column, null_int_column) VALUES('c', 3);
普通に通りました。
違和感を感じるけど驚かなくなってきた。
データを確認
select * from test_null; +------------------+-----------------+---------------------+--------------------+-------------------------+-----------------+ | null_char_column | null_int_column | notnull_char_column | notnull_int_column | notnull_char_column_add | notnull_int_add | +------------------+-----------------+---------------------+--------------------+-------------------------+-----------------+ | a | 1 | b | 2 | | 0 | | c | 3 | | 0 | | 0 | +------------------+-----------------+---------------------+--------------------+-------------------------+-----------------+
カラム追加の時と同じですね。
まとめ
not null 制約の付いたカラムを、データが存在するテーブルに対して追加する
mysql ○
※その場合の初期値は int なら 0、char なら ''
oracle ×
カラム追加のDDL文に NOT NULL を付ける時は同時に DEFAULT も付ける。
ex.)
ALTER TABLE tab_name ADD col_name NOT NULL DEFAULT 0;
postgres ×
oracleと違って NOT NULL と DEFAULT を同時に付ける事ができないので、
DEFAULT だけ付けてカラムを追加した後に、ALTER COLUMN で NOT NULL を追加します。
ex.)
ALTER TABLE tab_name ALTER COLUMN col_name DEFAULT 0;
ALTER TABLE tab_name ALTER COLUMN col_name SET NOT NULL;
のこり
not null カラムにmysqlが勝手に値を入れる、って事に強烈な根拠が欲しいんだけど、
もしかして default を指定するよりも早いのかな？
また今度検証してみよう。
