constraint と index の違い

Question

ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);

CREATE UNIQUE INDEX dist_id_zipcode_key ON distributors (dist_id, zipcode);

この二つには違いがあるのでしょうか。

chukenkenkou · Accepted Answer

#2回答者です。

「違い」について、追記します。

「違い」は、「表の定義の中で指定する」のと、「インデクスの定義で指定する」といったところです。表定義で指定すれば、DDLを逆生成した場合など、「インデクスの定義がどうなっているか？」といった手間が省けます。
その一方、表の定義そのものを変更するので、不慣れな場合はちょっと怖いかも。

一方、インデクス定義で作成する場合、表の定義自体はそのままなので、そういった不安はないと思います。

chukenkenkou · Answer

>create unique index の unique は、たまたまデータがユニークという特
>徴をもっているだけで、ユニークじゃないデータが発生した場合は、(この
>ままでは登録できないので) unique を外すことになるということだと考え
>ます。

それは違います。
create unique indexは、一意性を保つためにも使用されます。
create tableでのunique指定は、RDBMSによっては実装されていないものもあります。
「uniqueで定義できなければ、uniqueを外す」というのは、テーブル設計に失敗しているということになります。

SQLの標準化では、操作系SQLに比べ、定義系SQLは後回しになっており、各RDBMSで独自仕様が多く含まれる部分です。推測ですが、create tableのunique指定は後から標準SQLに追加されたのではないかと思います。
その一方で、create indexでのunique指定は、標準SQLには入っていなかったものの、商用RDBMSが出始めた頃から、多くのRDBMSに実装されていました。
そのため、古くからRDBMSを使っていた人は、create indexでunique指定することで重複禁止にする方法に慣れていると思います。

auty · Answer

ERROR:...
を３回出して、検証されているようです。

それらのメッセージは、当然そのまま受け入れてください。

お問い合わせの2点の違いのほかに、次のキーワードの考え方がが混乱に輪を掛けているようです。

・　UNIQUE 
・　レコードの重複

まず考えておかなければならないのは、この両者は、
　　　　　両立しない
と言うことです。その結果一方を先に設定すると他方は設定できなくなります。要するに
　　　　　設定の順番
が重要となります。

この考え方で、もう一度 ERROR:... を見直してみてください。

たとえば、３番目。
エラーの原因は、このとき既に重複したレコードを含んでいたわけです。
この重複したレコードの状態をすべてなくしてから実行してみてください。

alter table ... UNIQUE ...
は、成功します。

chukenkenkou · Answer

重複エラーになって当然です。

前者は表に対し、一意性をRDBMS側で保証してもらう指定です。主要なRDBMSでは、一意性を保証するための実装方式として、内部的に重複禁止のインデクスを作成します。もし、インデクスがなければ、重複チェックのために全件サーチが必要になってしまいますからね。

後者は、利用者側でインデクスを明示的に指定する方法です。インデクスはデータの絞込み、ソート抑止などの性能面の効果と、UNIQUE指定することで、重複データの存在を許さないようにできます。

auty · Answer

・　ADD CONSTRAINT はテーブルに制約を設けます。
　　この条件を満たさないレコードは、追加もされないし更新もされません。
　　つまり、違反するレコードは存在しないことが保証されます。
　　この場合、列 のペア(dist_id, zipcode)が重複することは許されません。

・　CREATE UNIQUE INDEX は、検索を速めるための機能です。
　　 UNIQUE キーワードを使用した場合、インデックスを構成する列 (dist_id, zipcode)が重複すると、インデックスを作成しません。 
　　つまりこのようなペアを持つ複数のレコードの存在は許されます。

constraint と index の違い

#2回答者です。

>create unique index の unique は、たまたまデータがユニークという特

ERROR:...

重複エラーになって当然です。

・ ADD CONSTRAINT はテーブルに制約を設けます。

この回答への補足

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

・　ADD CONSTRAINT はテーブルに制約を設けます。