制約っていらなくね?
そう思うときもあるけど、それがあるお陰で、 データの整合性が取れてるって面もあると思う。 んが、そのデータの整合が取れてるっていうのが、目に見えて 実感できないから、「制約ってめんどいな」とは、正直思う。 >>1 DB側で制御できたら、プログラム側で制御しなくて良いのだが。 >>8 はいはい。 おのれは、いずれホームレス^^ ただDBサーバーにアプリケーション機能を盛り込み過ぎるのもどぉーかなー >>11 その方が効率的だから、そうするのだろうが。 dbをISAM(索引順編成ファイル)としか使ってないの?w 制約いらないよね。 いらないから使わなければいいだけのこと。 制約にひっかかるデータは事前にチェックして、どの部分がまずいのかを 使う側に明確に表示させたいからね。 そのままエラー内容表示させるのもダサいしね。 >>13 同意。 そもそも、制約に引っかかるようなデータを書き込むアプリは、作りがアレだとおもうが >>13-14 ユニーク制約が、アプリ側でチェックできるんでつね? 重複チェックとは、別次元の問題だと思いまつが。。。 >>13 >使う側に明確に表示させたいからね。 >そのままエラー内容表示させるのもダサいしね。 制約違反はアプリにコールバックされるだろ そこからエラーメッセージ出せばいいだけじゃん・・・ >>16 そうだよね〜 あるマスタにないものを別のテーブルに入れてはいけない場合を考えてみよう プログラムがそれをやるとなると マスタの中に存在するか調べてから入れないといけなくなる 制約使えばエラーが発生するのでそこでメッセージ受け取って 適切なエラーメッセージクライアントに返すだけ ただ、最近では制約とか外部キーとか使わないものを推奨している雰囲気はある まぁ、あれだ。データベース設計やる奴とアプリ組む奴 がちゃんと意思疎通できる程度の規模のアプリなら問題 ないわけだ。大規模システムやアプリ開発を外注に出す 場合なんか、コミュニケーション不足やバグで整合性が 保たれないデータを作られないようにに、設計者がガー ドの意味で制約を張るのは当然だと思うよ。 >>18 そういう意味で制約を使うのなら納得。 でも、エラー処理をまかせるってのは、どうなんだろ。 いまどきは、そういうもんなの? >>19 >エラー処理を任せる ここで言ってるエラー処理って DBからエラー受け取って エラーメッセージを振り分けて表示するだけのことじゃないかな? 実際、制約あればエラーあった時点でDB更新は無いわけだし プログラミングは楽になる 本来データの重複や規定外の値が入るのを防ぐのは内部スキーマの担当で、それを 外部スキーマにやらせるならば、作成するすべての外部スキーマが エラーチェック->●エラーなら=>エラー表示してやり直し ●OKなら=>保存=>次行ってみよー という機構をもつ必要がある。そうすると外部スキーマの処理数が増えてバグを作りこむ 可能性も上がり、万一ひとつでもエラーチェックが甘い外部スキーマがあればそれのために 内部スキーマに整合性エラーが出かねない。 その点内部スキーマに持たせれば、外部スキーマは 保存->●エラーなら=>エラー表示してやり直し ●OKなら=>次行ってみよー と、エラーチェックの結果を判断するだけでよくなって万一バグがあってもランタイムエラーで済む。 アプリの作成中は良くても、今後増改築があったときにすべてのテーブルに対してどのような データを入れてはいけないか、忘れてしまってひとつでもエラーチェックの甘いアプリを 作ったら後が悲惨そう。 大体、もらったデータは例え候補キーが重複してたりNULLを含んでいても関係なく保存 していくDBMSって、「そこのコンビニ袋の中身冷蔵庫に入れといてくれよ」って言ったら 「うあぁぁ、カップラーメンも牛乳もガツンとみかんもみんな冷蔵庫に入れてあるうぅゥゥー!!!」 てなことするタコスケっぽくて情けなくない? >>21 俺もなるべくならDBにやらせたい派だな だってNullはDBの機能で入れられないようにするくせに 外部キー制約はプログラミングで実装ていうのはおかしいよな ただ、ひとつだけ もし、「あ、このデータ消したいな」と思った場合に困るんだよな そうした場合には制約ないほうがいいし 事実そうしているプロジェクトが多い >22 つ[ ON DELETE CASCADE ] つ[ ON DELETE SET NULL ] 今年のTE(DB)の試験では制約がいっぱい出ました まあ、論理設計の意図を反映させる意味で制約はつけてるけど。 正直、ユニーク制約なんかはユニークインデックス付ける方がよくやる。 NOT NULLは大嫌い。 何度ユーザーに裏切られた事か。 業界標準コードがNULLの商品データとか 本番稼動後になってから平気で寄越したり。 そもそも画面から更新処理を行おうとした時点で DB叩く前に入力チェックしなきゃいけないし。 未入力のフィールドにフォーカスあてたり フィールドごとにエラー文言変えたり。 データはなるべく静的な物として捉えたいので 業務ロジックを盛り込む事は避けたいです。 ただ、ユニークや外部キーなどは どちらかというとシステム寄りの要件なので 場合によってはつけた方がいいかなあとも 最近は思い直してます。 まぁシステムによりけりだね。 制約は断じて「業務ロジック」では無い。 でも設計がアレなシステムでは、制約が業務ロジック化してしまう。 制約自体に罪はない、設計がアレなだけだ、って事ですね。 今まで誤解してたよ、ごめんよ、制約。 全てアプリ側でコントロールすると >21みたいな事にもなるしね。 これからは心を入れ替えて制約も活用してみます。 皆さん具体的にどうですか? 制約使って便利だーいやトラブったーとかあります? 外部キーとかってどうしても使う勇気が湧かないんですよ。 ただ新しい事やりたくないってだけなんですが。 単にDBに値をチェックしてもらうだけならどうでもいいけど、関連レコードの自動削除みたいのはかなりいいと思うけどどうよ? 外部キーに ON DELETE CASCADE 付けとけば、親テーブルのレコードを削除するだけで紐付く子テーブルレコードも漏れなく削除できるよ。 ところで質問。 テーブルA とテーブルB があって、テーブルB はテーブルA の主キーを外部キーとして参照しています。 で、テーブルA のレコードに紐付くテーブルB が存在するかどうかを確認したいです。 できれば参照されている件数なんかも知ることができるといいです。 SELECT なりでも調べられますが、実は実際に扱おうとしているものは、テーブルA の主キーを外部キーとするテーブルがべらぼうに多くて、ちょっと大変です。 こんな感じになっちゃいます。 SELECT TABLE_A.ID, (SELECT COUNT(*) FROM TABLE_B WHERE TABLE_B.ID=TABLE_A.ID) +(SELECT COUNT(*) FROM TABLE_C WHERE TABLE_C.ID=TABLE_A.ID) +(SELECT COUNT(*) FROM TABLE_D WHERE TABLE_D.ID=TABLE_A.ID) : : FROM TABLE_A やりたいことってのは、どこからも参照されなくなった テーブルA レコードを削除したいのです。 なんかいい方法ありますか? >>29 TABLE_A.IDに ON DELETE NO ACCTION 付けて全行削除してみればいいんじゃないの? コストの問題なら、>>29 でどのくらい問題なのか書かんと。 >>33 現在はまだ実験してる段階なので、実際どのくらいの処理時間になるかはわかりませんが、やろうとしていることは次のようなものです。 このテーブルAはちょっとしたログテーブルみたいなもので、トランザクション毎に一行 INSERT されます。 で、そのトランザクション内で更新されたいろんなテーブルのレコードには、その TABLE_A.ID を記録します。 だからほっとくとテーブルAレコードは無限に増えて行きますし、TABLE_A.ID を外部キーとするテーブルの数も数十個あります。 また、更新を繰り返すうち、古いログ情報はどのテーブルからも参照されなくなるので、そうなったときには削除しなくてはなりません。 各テーブルの規模は、数十件くらいのものから数万件を超えそうなものまで様々です。 だから、>>29 にある SELECT で逐一被参照数をカウントするのは、ちょっと具合が悪そうだと推測できます。 まあ、必ずしも数を数える必要性は無いので、COUNT しようなんてことはせずに EXIST で各テーブルの参照の有無を調べて ばっさり DELETE してしまってもいいですが、WHERE には結局数十個のテーブルについての EXIST が並ぶことになり、 パフォーマンスは悪そうです。 DBの内部では、外部キーに参照されたレコードが削除されるときに、そのことを瞬時に把握する仕組みを持っているはずで、 それをなんとか利用する手段は無いのかと思っています。 ちなみに DB は PostgreSQL です。 システムテーブルについてもあれこれ調べてみたのですが、今のところめぼしい発見はありません。 テーブルAに被参照カウントを持って、各テーブルが更新されるときにトリガを使ってカウンタの自動更新をすることも考えましたが、 PostgreSQL はトリガ関数をお手軽に作れないようなのでちょっとアレです。 >>32 の内容はよくわからないのですが、テーブルAの全行を無条件に削除したら削除できないものだけ残るって やり方があるなら、そういうアプローチでもOKです。 ところで >>32 の ON DELETE NO ACCTION って、TABLE_A.ID につけるものじゃなくて、他のテーブルの外部キーに 付けるものではないのでしょうか。 で、NO ACTION が指定されてると、削除しちゃいけない行を削除したときにはエラーになって処理してもらえないと思いますが、 ちょっと勘違いしてますかね? 自己レスです。ちょっと思いついちゃいました。 テーブルAを参照する外部キーを、それ専用のテーブルにして、他のテーブルはそのテーブルを INHERITS すれば 具合がいい気がしてきました。 こんなかんじ。 CREATE TABLE_A ( ID INTEGER PRIMARY KEY, DATA TEXT ); CREATE TABLE_FK ( FK INTEGER NOT NULL REFERENCES TABLE_A(ID) ); CREATE TABLE_B ( SOMEDATA TEXT ) INHERITS(TABLE_FK); CREATE TABLE_C ( SOMEDATA TEXT ) INHERITS(TABLE_FK); そうすると、削除 TABLE_A の削除は DELETE FROM TABLE_A WHERE NOT EXISTS(SELECT 1 FROM TABLE_FK WHERE FK=TABLE_A.ID); みたいな感じですかね。 シンプルで速そうな気がしますが、いかがでしょう? でも、外部キーに参照されてるレコードなのか否かの判定方法は知りたいので、知ってる人がいましたらよろしくです。 たびたびすみません・・・ >>35 のやり方だと、TABLE_FK の外部キー制約は TABLE_B や TABLE_C に継承されないっぽいです。 TABLE_B、TABLE_C それぞれで外部キー制約を付ける必要があるようです。 ハマるところだった・・・ 危ない危ない。 >ばっさり DELETE してしまってもいいですが、WHERE には結局数十個のテーブルについての EXIST が並ぶことになり、 >パフォーマンスは悪そうです。 数十個といっても加算的なものだし。 逆にテーブルが3個しかないなら期待するパフォーマンスが出る という保証はないから、まずは計測してみれ。 >>34 を読む限りでは、テーブル3個の場合のパフォーマンスにも 満足できなそうに思えるんだが。 >DBの内部では、外部キーに参照されたレコードが削除されるときに、そのことを瞬時に把握する仕組みを持っているはずで、 ふつう、そんなものはない。 ON DELETE CASCADEも結局、トリガで対象のテーブルから deleteしてまわっているのと違いはない。 >>37 すみません、まだ計測できる段階じゃなくて・・・ もちろんテスト用にデータがそろえば計測してみます。 だけど、NOT EXISTS が AND でいっぱい並ぶってことは、削除対象レコードであることがわかるためには 対象テーブルの全行を全検索することになるので、どうなんだろうと思うわけです。 INDEX 付ければ速くなるでしょうが、ログテーブルを逆引きするためにいちいち INDEX の領域とるのはなぁ、 と躊躇してしまいます。 といってもそれは、他になにか手段があるはずだと思ってるから、選択肢として後回しにしてるに過ぎない のですが。 >>DBの内部では、外部キーに参照されたレコードが削除されるときに、そのことを瞬時に把握する仕組みを持っているはずで、 > >ふつう、そんなものはない。 そうなんですかね? ON DELETE CASCADE 指定されたときはいざ知らず、ただの外部キーにより参照されてるカラムは、内部的に 被参照カウンタでも持ってるんじゃないかな、と勝手に推測したのですが。 というのは、>>35 のやり方をやったとき、 TABLE_FK には当然 TABLE_A に無い値は INSERT できないし、逆に TABLE_FK にある値は TABLE_A から削除できません。 これは期待する動作ですよね。 ところが TABLE_FK を継承した TABLE_B、TABLE_C へは、 TABLE_A に無い値でも INSERT できてしまいます。 で、TABLE_FK を SELECT してみれば、やっぱり TABLE_A に無い値を持つレコードができてしまっています。 たとえばその値が 100 だったとして、関節的にTABLE_FK に TABLE_A に無い 100 を INSERT して、次に TABLE_A に 100 を INSERT して、さらに TABLE_A からその 100 を DELETE してみると、TABLE_FK に 100 を持つ値があるのに DELETE できてしまいます。 ということは、TABLE_A が削除されるときに、外部キー参照しているテーブルをいちいち検索しに行ってるわけでは ないのかな、と思ったのですが、いかがでしょう? しかし、継承させたテーブルで継承元の制約が受け継がれないってのは、一般的な仕様なのでしょうか。 それとも PostgreSQL のバグですかね? >>38 TABLE_B.IDとかには当然indexを設定していると思っていたんだが。 >INDEX 付ければ速くなるでしょうが、ログテーブルを逆引きするためにいちいち INDEX の領域とるのはなぁ、 本当にdeleteのパフォーマンスが問題ならindexを作る。パフォーマンスを 犠牲にしてでも領域を節約する必要があるなら作らない。 ディスク領域を気にしているようだが、じゃあこれらのテーブルにどのくらいの レコードが登録されてどれくらい領域を必要とするか見積もってる? チューニングの話なら「遅そう」「領域食いそう」とかの感覚的な話じゃ 先に進まないよ。そのへん見積もれないうちから小手先のテクニックを 弄しても無駄に終わる可能性が高いから、まずは正攻法でやってみれ。 >ということは、TABLE_A が削除されるときに、外部キー参照しているテーブルをいちいち検索しに行ってるわけでは >ないのかな、と思ったのですが、いかがでしょう? FKとB、Cは別のテーブルであって、FKをselectした際にデフォルトで BとCも一緒に検索してるだけ。Aにない値がFKに入ったわけではない。 >しかし、継承させたテーブルで継承元の制約が受け継がれないってのは、一般的な仕様なのでしょうか。 >それとも PostgreSQL のバグですかね? 「一般的な仕様」などはない。それはバグじゃなくてPostgresの仕様。 たしかに、継承するなら制約やトリガなども継承された方が便利だと 思うんだけどね。 >>29 もう少し手短にまとめてくれ。読むのがめんどくさい。 一般的に速度と記憶域はトレードオフ。実際に手を動かさずに、いつまでも机上の 空論を弄んで、根拠のない妄想に見切りつけずにあれこれ悩むのは時間の無駄。 そもそも更新速度の低下でならまだしも、記憶域を圧迫する理由でインデックスを 張るのを躊躇するような貧相な環境なら、まず環境を見直すべき。 >>39 ,40 文章得意じゃなくて、端的に書けなくてスマソ。 INDEX を張るのを躊躇した理由は、容量の問題だけではなくて更新時のパフォーマンスも気にしてのことです。 さらに言うと、実はTABLE_A.ID への外部キーは、一つのテーブルあたり挿入時用、更新時用、削除マーク用の 3つあるので、 容量もINDEX更新のコストも3倍かかりそうで、なおさら気になったのです。 自分はINDEXに必要なディスクスペースや、INDEX更新に必要なコストを見積もるスキルは無いのですが、せっかく制約の スレを見つけたので、制約に関する便利機能なんかの意見をもらえるかな、と思っての質問でした。 自分でも結局は INDEX 張って正攻法でやることになるんだろうな、と思っています。 > たしかに、継承するなら制約やトリガなども継承された方が便利だと > 思うんだけどね。 ですよね。 そのほうがエレガントだと思うんですけど、PostgreSQL 7.0 から 7.1 への変更に「継承先のテーブルの継承列で主キー, 外部キーが定義できるようになりました.」とあるので、やっぱり仕様として意図的にこうなってるんですよね。 ところで > FKとB、Cは別のテーブルであって、FKをselectした際にデフォルトで > BとCも一緒に検索してるだけ。Aにない値がFKに入ったわけではない。 EXPLAIN で TABLE_FK をSELECT するクエリプランを見てみたら、本当に TABLE_B、TABLE_C の SELECT も やってるんですね。 実はテーブルの継承って言葉はここ2〜3日で覚えたので、新しい発見だらけです。 このスレで話してよかったです。 でも、ということは、INDEX を継承元テーブルに張るだけではなく、それを継承するテーブルにも INDEX を張らないと ダメなんですね? うーん、めんどくさいなぁ・・・ そう思うと、テーブルを継承するメリットってなんなんでしょうかね? 制約やデフォルト値を細かく指定しておくと ドキュメントがないとか,前任者が遁走した時の場合でも ある程度何をしたかったかがわかるのでよい >>42 うんうん、わかるわかる(泣)。 俺、火消し役ばっかりやらされてるんで。 でも、遁走するような奴の設計には 懐疑的になってしまうのもまた事実ですね。 でも何も無いよりいいか。 適当な設計やっても平気で提案できるくらいの奴は、 神経が図太い場合が多いから、逆に辞めない。 間違った仕様を正したいのに、認められなくて 耐えられなくなって辞める奴の方が多い。 憎まれっ子世にはばかる。 関係ないけど、火消しを入れる予算があるのなら、 最初から出火させない人を雇えよと思ったりもする。 火消し担当の人が、最初からプロジェクトに関わればいいのに。 >>45 そういった事が出来れば火消しなんか使わないかと。 むしろ火消しが出来る人(又はグループ)を 予備選力としてヤバい所に投入ってのは防御の基本かと。 (史実の重戦車大隊みたいだな) >>45 あたくしの場合、火消し役やらされてる間も 他の案件の保守で人月工数出とるんで コストかかっとらんのですわ。 だから便利に使われるって訳です。疲れた・・・・。 納期間に合わなさそう ↓ 新しい人材を大量投入 ↓ スキル高い人の手間が、新しい人への引継や教育に取られる ↓ さらに炎上 こういうパターンが多いよな。 うちでは、主キー以外の制約はってません それはISAM的なんでしょうか? なんかDBMSの機能を使ってるきがしない 関連削除も不具合が発見されてから、整合性調整アプリを作ってるし みんなはきちんと関連図みたいなんカタメテからやってるんですよね? 実はうちもです。 カスケードってなーに? 頑張ってアプリ側で実装ですよ。 あとから設計見てどういう意図で組んだのかわかりやすいから制約は付けてるなあ どーせ突っ込む前にデータチェックするんだけどね 制約かぁ・・・ 参照性合成制約とかトリガーによるチェックはどうかと思うが、 主キーとNOT NULL制約は最低限付けた方が良いな。 1 WEBからデータ入力(クライアントでのデータチェック) 2 サーバプログラムで加工(サーバでのデータチェック) 3 DBに格納(DBMSでのデータチェック) なんかもどかしい. 保守する時に、 参照整合性制約サイコー。 トリガーは糞。 と思う事が多いのだが、 どうか? >>56 参照整合制約って使った事ないけど 保守する上でどうサイコー? 本で読むような利点じゃなくて 現場の濃い話希望。 売春婦(DB)とその客(アプリ)に例えれば・・・。 入れるときに相手にゴムの着用を求めても、それが万全とはいえないし、そもそもつけるかも強制できない。 そうであれば、自分で制約(避妊手段)を準備しておくのが、変なデータを作らないための予防策。 >>57 56じゃないが俺はゴミが入らないのが利点だと思う。 これ以上でもこれ以下でもない。 ありえないデータが入ってるDBなんざみたくねぇ!! トリガーは便利だと思うけどな。 そのありえないデータを削除したりするのによく使う。 トリガーって、ER図から読み取れないから怖いやね。 時刻更新とか、極簡単で引継ぎしやすい物であれば便利だと思う。 スパープログラマな人が、作ったオレサマトリガーは、簡便∩( ・ω・)∩ 参照整合性制約を張るとインデックスが張られるやね。 そのER図をプログラマに配っておけば、自然とコチラの意図通りの インデックスを使ったSQLを書いてくれるといいなぁ。。。 (そこそこのレベルの人なら分かってくれる。) 他には、 ・参照整合性制約に基づいた連鎖更新・削除が便利。 ・Access2003のXMLエクスポート機能を使う場合に 参照整合性制約に基づいて親子テーブルを エクスポート出来るとか。 中小規模で、レスポンス性能とかシビアでなければ 参照整合性制約付けといた方が管理しやすいと思う。 問題になれば、外すだけで性能アップさせましたと 言えるかもしれないし。。( ̄ー ̄)ニヤリ 現場の濃い話って、↓こんなのか? 中小SI ヘッポコシステム 客「このデータ消したいなぁ」 担「何度も申し上げた通り(略」 客「前の担当者なら(略」 上司「ヤレ!」 担「しかしDBを直接弄るのは、(略」 上司「この辺のデータを(略」 担「...」 客「データがオカシイゾ(略」 上司「誰がそんな事をヤレと(略」 担「...」 >>59 ゴミの排除を制約に全部まかせちゃうんですか? 恐いなーと思って。 何がゴミかとかありえないデータかってのは 業務要件だったりする事が多いわけで それはアプリ側で実装されてた方がわかり易いし 管理も楽だと思うんですよ。 で、制約をつけると、再実装になるし 保守も難儀な事になりそう。 そうでもないのかな? read.cgi ver 07.5.1 2024/04/28 Walang Kapalit ★ | Donguri System Team 5ちゃんねる