ようこそ ゲスト さん、新規登録（無料）して気になる疑問を解決しませんか？
はじめての方へ｜ヘルプ｜FAQ

質問
質問者：t-okura 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);

この二つには違いがあるのでしょうか。
質問投稿日時：08/04/18 20:45
質問番号：3957955
この質問に対する回答は締め切られました。
最新から表示｜回答順に表示｜良回答のみ表示

回答

 
回答者：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指定することで重複禁止にする方法に慣れていると思います。
種類：アドバイス
どんな人：専門家
自信：参考意見
回答日時：08/04/19 16:58
回答番号：No.5
この回答へのお礼補足 ありがとうございます。

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

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

回答

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

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

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

・　UNIQUE
・　レコードの重複

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

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

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

alter table ... UNIQUE ...
は、成功します。
種類：アドバイス
どんな人：経験者
自信：参考意見
回答日時：08/04/19 06:48
回答番号：No.4
この回答へのお礼回答ありがとうございます。

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

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

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

回答

良回答20pt
回答者：chukenkenkou #2回答者です。

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

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

一方、インデクス定義で作成する場合、表の定義自体はそのままなので、そういった不安はないと思います。
種類：アドバイス
どんな人：専門家
自信：参考意見
回答日時：08/04/18 23:07
回答番号：No.3
この回答へのお礼add constraint は、表の持つデータの正当性を保障するため制約するも
の。表自身の定義の一部となる。
create index は、検索等を効率よく行うため、データにインデックスを
つけるもの。unique はインデックス付けする際のデータの特徴をあらわ
すもので、表自身とは別に定義されるもの。

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

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

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

回答

 
回答者：chukenkenkou 重複エラーになって当然です。

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

後者は、利用者側でインデクスを明示的に指定する方法です。インデクスはデータの絞込み、ソート抑止などの性能面の効果と、UNIQUE指定することで、重複データの存在を許さないようにできます。
種類：アドバイス
どんな人：専門家
自信：参考意見
回答日時：08/04/18 22:54
回答番号：No.2
この回答へのお礼この回答にお礼をつける(質問者のみ)

回答

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

・　CREATE UNIQUE INDEX は、検索を速めるための機能です。
　　 UNIQUE キーワードを使用した場合、インデックスを構成する列 (dist_id, zipcode)が重複すると、インデックスを作成しません。
　　つまりこのようなペアを持つ複数のレコードの存在は許されます。
種類：アドバイス
どんな人：経験者
自信：参考意見
回答日時：08/04/18 21:40
回答番号：No.1
この回答への補足なるほど! と思ったのですが、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.
というエラーになりました。
この回答へのお礼この回答にお礼をつける(質問者のみ)
最新から表示｜回答順に表示｜良回答のみ表示
このページのトップへ