テーブルにデフォルトでNLLLを入れますか？

Question

テーブルに以下
nameA、nameB のいずれかが入るカラムがあるとします。
mytable2_ID、my_table3_ID のいずれかが入るカラムがあるとします。

CREATE TABLE IF NOT EXISTS mytable1 (
ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
mytable2_ID INT(10), # いずれかが入る
mytable3_ID INT(10),  # いずれかが入る
FOREIGN KEY (mytable2_ID) REFERENCES mytable2(ID),
FOREIGN KEY (mytable3_ID) REFERENCES mytable3(ID)

こういった場合、下記のようにDEFAULT NULLを設定すべきでしょうか？

CREATE TABLE IF NOT EXISTS mytable1 (
ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
mytable2_ID INT(10) DEFAULT NULL,
mytable3_ID INT(10) DEFAULT NULL,
FOREIGN KEY (mytable2_ID) REFERENCES mytable2(ID),
FOREIGN KEY (mytable3_ID) REFERENCES mytable3(ID)

いまいちNULLのメリットやデメリットが理解できておりません…
あると何が便利なのでしょうか？
###修正
誤解があったため内容を次のように修正させていただきます。
「NULL」か「空文字列」か、どちらにすべきか？でした。
ご指摘頂いたmaisumakun様、ありがとうございます。

Answer 1

値が与えられなければ、必然的にNUllになるので、敢えて指定する必要は無く、また敢えてDEFAULT Nullにする場面は思いつきません。

例に挙げているテーブルで、何れか一方がNot Nullでもう一方はNUllという事なら、それは制約で定義するものですし、設計としてなら、値とその識別（A or B）というテーブル設計であるべきでしょう。

追記

質問の内容から、用いられるSQLは以下を想定されているのだと思います。
A.

select *
from  mytable1 t1
      left join mytable2 t2
      on  t1.mytable2_ID=t2.id
      left join mytable3 t3
      on  t1.mytable3_ID=t3.id

この場合、外部キー制約により片側一方が設定されない場合の値（例えば０）をmytable2 や3に値として登録しておく必要があります。

B.

テーブル構造を

CREATE TABLE IF NOT EXISTS mytable1 (
ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
type int(1)　not null, -- mytable_idの参照先(0:mytable2, 1:mytable3)
mytable_ID INT(10) not null
)

のようにした場合、外部キーの設定はできませんが、必須が担保されるので、default値は不要になります。
また、問い合わせは以下の様になります。

select *
from  mytable1 t1
      left join mytable2 t2
      on  t1.type=0 and t1.mytable_ID=t2.id
      left join mytable3 t3
      on  t1.type=1 and t1.mytable_ID=t3.id

**A.**の問い合わせに関して適切にインデックスを適用しようとするなら
(mytable2_ID),(mytable3_ID)の2つのインデックスが必要です。
※(mytable2_ID , mytable3_ID)のインデックスでは効率的ではありません。
この２つのインデックスを適用する為にはunionを使用する必要があります。

select ～
from  mytable1 t1
      inner join mytable2 t2
      on  t1.mytable2_ID=t2.id
union all
select ～
from  mytable1 t1
      inner join mytable3 t3
      on  t1.mytable3_ID=t3.id

union ですから、当然select項目は数を合わせなければなりません。

一方、**B.**の問い合わせ関しては、(type, mytable_ID)のインデックスを用意するだけです。

Comments

[yakan]

>値が与えられなければ、必然的にNUllになる
なるほどそうでしたか。「NULL」を指定しなければ「空」という状態があると思っていました。

>何れか一方がNot Nullでもう一方はNUllという事なら、それは制約で定義する
こちらとても知りたいです。「いずれかは必須」という制約はどのようにするのでしょうか？

[sazi]

DBMSは何ですか？
お薦めは、（識別, 値)で共にNot null 制約ですが。

[sazi]

>「いずれかは必須」という制約はどのようにするのでしょうか？
以下でいうCHECK制約です。

SQLの制約の種類とその指定方法
https://qiita.com/Uejun/items/de02b78adbc54868d40d

[yakan]

失礼いたしました。MySQL5.7です。

>（識別, 値)で共にNot null 制約
といいますと…？

現状では「いずれかは必須」という制約のためには、すべてを複合主キーにする手しか思いうかばず、しかも実際には mytable2_ID、mytable3_ID だけでなく、3,4,5と5種類ございまして、これら５つをまとめて複合主キーにするのはどうかと思い、その制約は諦めていたところでして、ぜひとも知りたいです。何か参考になりそうなキーワードでも教えて頂けませんでしょうか？調べてみたいと思います。

[yakan]

>以下でいうCHECK制約です
ありがとうございます！調べてみます！

[sazi]

(NameA, NameB)一方のみが必須ということなら、(type, Name)でNameの内容がNameAを表すならTYPE='A'とすることです。
この項目が検索や結合対象であるなら、構造を変更する事でインデックスも効率的なものになります。

[yakan]

仰るご指定がわからず、調べた感じで実装しようとしたのですが…制約違反でも普通に成功してしまいました。もしお手すきでしたら以下アドバイス頂戴できますと幸いです。
http://teratail.com/questions/273776

[yakan]

↑の件ですが、普通にバージョンの問題でできなかったようでした。連投質問失礼いたしました。

[sazi]

>「NULL」か「空文字列」か、どちらにすべきか？でした。
項目がint型なら空文字は設定できないので、外部参照している項目なら、default 0 でしょうね。
だけど、それを許すなら、参照整合性はどうしますか？
参照整合性までを求めないのならインデックスだけの問題ですから、お勧めした構造の方が良いと思いますけど。

[yakan]

外部参照というのは外部キーの参照ということですよね。確かに、そういえば外部キーは「NULL」は許容できても「''」は許容できませんから、外部キーが設定できないということですね。ならばやはり「NULL」でいきます。ご相談乗って頂き誠にありがとうございました。

[yakan]

ご追記ありがとうございます！

>用いられるSQLは以下を想定されているのだと思います。
仰るとおりの構造で考えておりました。

typeの方法は確かに…なるほどです。そうなりますと、
・外部キーによってテーブル間の整合を担保するか
・それともtypeの方法で必須を担保するか
大変悩みます。

ただ思ったのですが、必須の方はPHPでできますが、整合の方はSELECTをかけないといけませんよね。具体的なコードですと、

必須の方は以下のように瞬時に済む一方で
if( empty($val2) && empty($val3) )[
die();
}else{
// 保存実行
}

整合の方は以下のように0.0001秒くらいかかると思います。
$row2 = $pdo->query("SELECT * FROM mytable2 WHERE ID = $val2");
$row3 = $pdo->query("SELECT * FROM mytable3 WHERE ID = $val3");
if( empty($row2) && empty($row3) )[
die();
}else{
// 保存実行
}

そう考えますと、typeの方法よりも外部キーの方が速度の面でいえば良いのかな、とも思ったのですが、この辺りいかがなものでしょうか。

[sazi]

追記しました。
あくまで理論上ですので、ある程度の件数のデータを用いた実行計画で、確認した方が確実ですけど。

[sazi]

> 整合の方はSELECTをかけないといけませんよね。
mytable2 や3に整合を掛ける必要があるとするなら、その場合の操作としては、mytable2 や3の内容を選択させるような処理になるのではないですか？
それなら選択する時点で整合はとれますので、さらにチェックするのは冗長です。

[yakan]

あれもこれも仰る通りで、いろいろなケースを考えてみましたがすべて選択してからINSERTされるものでしたので、おかげ様で外部キーが冗長だったと気付けました。typeの構造で進めていきたいと思います。度々のご返信、誠にありがとうございました。

Answer 2

例えば氏名の場合、日本では姓と名ですが、欧米ではミドルネームが存在しますね。
しかもミドルネームはない人もいます。
※日本人ハーフの場合、姓か名のどちらかにミドルネームをくっつけてますね。田中マルクス闘莉王さんとか、ケンブリッジ飛鳥アントニオさんとか。

ですからテーブル定義上、ミドルネームは null を許可しなくてはならないでしょう。
※別の手としてはミドルネームだけ別テーブルにして、1対 (0or1） の結合をとる手もありますが

むろんこのような場合にミドルネームを空文字列（''）として登録する運用でもできなくはないですが。

すでに他の方が書いていますが、質問のような場合は、テーブル設計を見直すべきではあります。
nameA と nameB のどちらかしか入らないなら、name と「どちらに入れるのか」を記録するようにするとか。

Comments

[yakan]

>ミドルネームは null を許可しなくてはならないでしょう
なるほど、カラムの値としては「null」以外に「空」という何も入っていない状態があるものと思っておりました。しかしそういった状態はなく、何も入っていなければそれは強制的に「null」になるのですね。

[tacsheaven]

データベースにおいて「null」とは、特別な状態です。それは本当に「何もない」ことを示しているのであって、空文字列（''）とも違うものなのです。
null は何と比較しても true にも false にもならず null になる（null とでさえも）ので、扱いには注意が必要です。
※where {カラム} = null は、いかなる場合でも成立しない。そのために特別な演算子 is null があったり、MySQL のように null 比較安全な演算子 <=> が定義されていたりする

[yakan]

だから IS NULL が必要だったのですね。いつも where {カラム} = null ができないことに納得できていなかったのでスッキリいたしました。

Answer 3

下記のようにDEFAULT NULLを設定すべきでしょうか？

逆に質問しますが、両方の列をNOT NULLに設定して、値が入らないところには何を入れるつもりなのでしょうか？

「テーブルを分けない」条件では、NULLを許容する他ありません。

Comments

[yakan]

>両方の列をNOT NULLに設定して
nameA、nameBはいずれかが入るカラムなので、「両方の列をNOT NULLに設定」はしないです。

>値が入らないところには何を入れるつもりなのでしょうか？
DEAFAULT NULLを指定しないという意味で、特に何も入れず、「空」という状態にしようと考えています。

>NULLでない「空」という状態は、そもそも存在しません
なるほど、DEAFAULT NULLを指定しなければ、それは自動でDEAFAULT NULLになるのですね。

[yakan]

あれ？
やはり「空」という状態はありました。下図の赤がそれで、緑が「NULL」です。
https://imgur.com/a/ON5PGZW

[maisumakun]

空文字列が入っている状態ではないでしょうか？

[yakan]

なるほどこれは「空文字列」という状態でしたか。それでは質問を変えなければなりませんね…
「NULL」にすべきか？「空文字列」にすべきか？というのが質問したかったことでした。
そうなりますといかが思われますでしょうか？
何度も申し訳ございません。

Answer 4

いまいちNULLのメリットやデメリットが理解できておりません…
あると何が便利なのでしょうか？

まず、基本的にNULLの完全に排除する事は非常に難しいものの、極力利用を避けるように設計する方が望ましいという意見がありますし、個人的にも概ね同意します。

ちょっとググれば色々な意見が出てくると思いますが、例えばNULL撲滅委員会などを読んでみて下さい。

また、質問文の場合はそもそものテーブル設計がおかしい可能性があります。どのようなエンティティかわからないので一概には言えませんが、nameAとnameBはそれぞれ別テーブルに分けて必要に応じてJOINするような設計の方が直感的には良さそうな気がします。

Comments

[yakan]

「極力利用を避けるように設計する方が望ましい」ですが、それは「空」のままという意味ですか？
それとも後半で仰っているように別テーブルにして「NOT NULL」にすべきという意味ですか？

後者ならば、今回はテーブルを別にわけない場合についてお聞きしたいと思っていたのですが、「空」か「NULL」かいずれがよろしいでしょうか？

[maisumakun]

> 後者ならば、今回はテーブルを別にわけない場合についてお聞きしたいと思っていたのですが、「空」か「NULL」かいずれがよろしいでしょうか？

NULLでない「空」という状態は、そもそも存在しません。NOT NULLであれば、何かしらの値を詰める必要があります。