constraint と index の違い

質問

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);

この二つには違いがあるのでしょうか。

回答No.3

chukenkenkou · ベストアンサー

#2回答者です。

「違い」について、追記します。

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

一方、インデクス定義で作成する場合、表の定義自体はそのままなので、そういった不安はないと思います。

お礼

add constraint は、表の持つデータの正当性を保障するため制約するも
の。表自身の定義の一部となる。
create index は、検索等を効率よく行うため、データにインデックスを
つけるもの。unique はインデックス付けする際のデータの特徴をあらわ
すもので、表自身とは別に定義されるもの。

機能的には同じような働きをするが、目的が違うと理解しました。

create unique index の unique は、たまたまデータがユニークという特
徴をもっているだけで、ユニークじゃないデータが発生した場合は、(この
ままでは登録できないので) unique を外すことになるということだと考え
ます。

ありがとうございました。

回答No.5

chukenkenkou

>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指定することで重複禁止にする方法に慣れていると思います。

お礼

補足 ありがとうございます。

> 「uniqueで定義できなければ、uniqueを外す」というのは、テーブル設
> 計に失敗しているということになります。

おっしゃることはわかります。unique 制約のないデータに unique index を
作るということが、そもそも間違いなのでしょうね。

回答No.4

auty

ERROR:...
を３回出して、検証されているようです。

それらのメッセージは、当然そのまま受け入れてください。

お問い合わせの2点の違いのほかに、次のキーワードの考え方がが混乱に輪を掛けているようです。

・　UNIQUE
・　レコードの重複

まず考えておかなければならないのは、この両者は、
　　　　　両立しない
と言うことです。その結果一方を先に設定すると他方は設定できなくなります。要するに
　　　　　設定の順番
が重要となります。

この考え方で、もう一度 ERROR:... を見直してみてください。

たとえば、３番目。
エラーの原因は、このとき既に重複したレコードを含んでいたわけです。
この重複したレコードの状態をすべてなくしてから実行してみてください。

alter table ... UNIQUE ...
は、成功します。

お礼

回答ありがとうございます。

質問のタイトル「constraint と index の違い」がまずかったと反省してい
ます。このタイトルだと最初にいただいた回答のとおりだと思います。

ただ、今回は unique constraint と unique index の違いがわからなかった
ため、質問させていただいたものでした。

unique というキーワードは、制約を課す constraint では本質的なもの、
効率化を図る index では補助的なものと理解しました。

回答No.2

chukenkenkou

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

前者は表に対し、一意性をRDBMS側で保証してもらう指定です。主要なRDBMSでは、一意性を保証するための実装方式として、内部的に重複禁止のインデクスを作成します。もし、インデクスがなければ、重複チェックのために全件サーチが必要になってしまいますからね。

後者は、利用者側でインデクスを明示的に指定する方法です。インデクスはデータの絞込み、ソート抑止などの性能面の効果と、UNIQUE指定することで、重複データの存在を許さないようにできます。

回答No.1

auty

・　ADD CONSTRAINT はテーブルに制約を設けます。
　　この条件を満たさないレコードは、追加もされないし更新もされません。
　　つまり、違反するレコードは存在しないことが保証されます。
　　この場合、列 のペア(dist_id, zipcode)が重複することは許されません。

・　CREATE UNIQUE INDEX は、検索を速めるための機能です。
　　 UNIQUE キーワードを使用した場合、インデックスを構成する列 (dist_id, zipcode)が重複すると、インデックスを作成しません。
　　つまりこのようなペアを持つ複数のレコードの存在は許されます。

補足

なるほど! と思ったのですが、PostgreSQL 8.1 で
create unique index し、列のペア(dist_id, zipcode) が重複する
データをインサートしてみたところ、
ERROR: duplicate key violates unique constraint "dist_id_zipcode_key"
となりました。

また、すでに重複するペアを持つテーブルで create unique index すると
ERROR: could not create unique index
DETAIL: Table contains duplicated values.
のようになり、インデックスが作成できませんでした。

alter table ... add constraint した場合は、
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "dist_id_zipcode_key" for table "distributors"
ERROR: could not create unique index
DETAIL: Table contains duplicated values.
というエラーになりました。