制約っていらなくね? (114レス)
制約っていらなくね? http://mevius.5ch.net/test/read.cgi/db/1087483786/
上
下
前
次
1-
新
通常表示
512バイト分割
レス栞
27: NAME IS NULL [ ] 04/07/08 22:36 ID:??? まぁシステムによりけりだね。 制約は断じて「業務ロジック」では無い。 でも設計がアレなシステムでは、制約が業務ロジック化してしまう。 http://mevius.5ch.net/test/read.cgi/db/1087483786/27
28: 26 [sage] 04/07/09 10:17 ID:??? 制約自体に罪はない、設計がアレなだけだ、って事ですね。 今まで誤解してたよ、ごめんよ、制約。 全てアプリ側でコントロールすると >21みたいな事にもなるしね。 これからは心を入れ替えて制約も活用してみます。 皆さん具体的にどうですか? 制約使って便利だーいやトラブったーとかあります? 外部キーとかってどうしても使う勇気が湧かないんですよ。 ただ新しい事やりたくないってだけなんですが。 http://mevius.5ch.net/test/read.cgi/db/1087483786/28
29: NAME IS NULL [] 04/07/10 18:41 ID:+BBjcTFg 単に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 レコードを削除したいのです。 なんかいい方法ありますか? http://mevius.5ch.net/test/read.cgi/db/1087483786/29
30: NAME IS NULL [sage] 04/07/10 19:04 ID:??? 大変つっても一回書きゃおしまいだろ? http://mevius.5ch.net/test/read.cgi/db/1087483786/30
31: NAME IS NULL [sage] 04/07/10 19:09 ID:??? >>30 処理にかかるコストの問題です。 http://mevius.5ch.net/test/read.cgi/db/1087483786/31
32: NAME IS NULL [sage] 04/07/10 21:46 ID:??? >>29 TABLE_A.IDに ON DELETE NO ACCTION 付けて全行削除してみればいいんじゃないの? http://mevius.5ch.net/test/read.cgi/db/1087483786/32
33: NAME IS NULL [sage] 04/07/10 21:55 ID:??? コストの問題なら、>>29でどのくらい問題なのか書かんと。 http://mevius.5ch.net/test/read.cgi/db/1087483786/33
34: 29 [sage] 04/07/10 22:20 ID:??? >>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 が指定されてると、削除しちゃいけない行を削除したときにはエラーになって処理してもらえないと思いますが、 ちょっと勘違いしてますかね? http://mevius.5ch.net/test/read.cgi/db/1087483786/34
35: 29 [sage] 04/07/10 22:59 ID:??? 自己レスです。ちょっと思いついちゃいました。 テーブル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); みたいな感じですかね。 シンプルで速そうな気がしますが、いかがでしょう? でも、外部キーに参照されてるレコードなのか否かの判定方法は知りたいので、知ってる人がいましたらよろしくです。 http://mevius.5ch.net/test/read.cgi/db/1087483786/35
36: 29 [sage] 04/07/10 23:18 ID:??? たびたびすみません・・・ >>35 のやり方だと、TABLE_FK の外部キー制約は TABLE_B や TABLE_C に継承されないっぽいです。 TABLE_B、TABLE_C それぞれで外部キー制約を付ける必要があるようです。 ハマるところだった・・・ 危ない危ない。 http://mevius.5ch.net/test/read.cgi/db/1087483786/36
37: 名無しさん@そうだ選挙に行こう [sage] 04/07/11 09:19 ID:??? >ばっさり DELETE してしまってもいいですが、WHERE には結局数十個のテーブルについての EXIST が並ぶことになり、 >パフォーマンスは悪そうです。 数十個といっても加算的なものだし。 逆にテーブルが3個しかないなら期待するパフォーマンスが出る という保証はないから、まずは計測してみれ。 >>34を読む限りでは、テーブル3個の場合のパフォーマンスにも 満足できなそうに思えるんだが。 >DBの内部では、外部キーに参照されたレコードが削除されるときに、そのことを瞬時に把握する仕組みを持っているはずで、 ふつう、そんなものはない。 ON DELETE CASCADEも結局、トリガで対象のテーブルから deleteしてまわっているのと違いはない。 http://mevius.5ch.net/test/read.cgi/db/1087483786/37
38: 29 [sage] 04/07/11 10:23 ID:??? >>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 のバグですかね? http://mevius.5ch.net/test/read.cgi/db/1087483786/38
39: 名無しさん@そうだ選挙に行こう [sage] 04/07/11 12:15 ID:??? >>38 TABLE_B.IDとかには当然indexを設定していると思っていたんだが。 >INDEX 付ければ速くなるでしょうが、ログテーブルを逆引きするためにいちいち INDEX の領域とるのはなぁ、 本当にdeleteのパフォーマンスが問題ならindexを作る。パフォーマンスを 犠牲にしてでも領域を節約する必要があるなら作らない。 ディスク領域を気にしているようだが、じゃあこれらのテーブルにどのくらいの レコードが登録されてどれくらい領域を必要とするか見積もってる? チューニングの話なら「遅そう」「領域食いそう」とかの感覚的な話じゃ 先に進まないよ。そのへん見積もれないうちから小手先のテクニックを 弄しても無駄に終わる可能性が高いから、まずは正攻法でやってみれ。 >ということは、TABLE_A が削除されるときに、外部キー参照しているテーブルをいちいち検索しに行ってるわけでは >ないのかな、と思ったのですが、いかがでしょう? FKとB、Cは別のテーブルであって、FKをselectした際にデフォルトで BとCも一緒に検索してるだけ。Aにない値がFKに入ったわけではない。 >しかし、継承させたテーブルで継承元の制約が受け継がれないってのは、一般的な仕様なのでしょうか。 >それとも PostgreSQL のバグですかね? 「一般的な仕様」などはない。それはバグじゃなくてPostgresの仕様。 たしかに、継承するなら制約やトリガなども継承された方が便利だと 思うんだけどね。 http://mevius.5ch.net/test/read.cgi/db/1087483786/39
40: 名無しさん@そうだ選挙に行こう [sage] 04/07/11 16:54 ID:??? >>29 もう少し手短にまとめてくれ。読むのがめんどくさい。 一般的に速度と記憶域はトレードオフ。実際に手を動かさずに、いつまでも机上の 空論を弄んで、根拠のない妄想に見切りつけずにあれこれ悩むのは時間の無駄。 そもそも更新速度の低下でならまだしも、記憶域を圧迫する理由でインデックスを 張るのを躊躇するような貧相な環境なら、まず環境を見直すべき。 http://mevius.5ch.net/test/read.cgi/db/1087483786/40
41: 29 [sage] 04/07/12 02:30 ID:??? >>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 を張らないと ダメなんですね? うーん、めんどくさいなぁ・・・ そう思うと、テーブルを継承するメリットってなんなんでしょうかね? http://mevius.5ch.net/test/read.cgi/db/1087483786/41
42: NAME IS NULL [sage] 04/07/21 01:04 ID:??? 制約やデフォルト値を細かく指定しておくと ドキュメントがないとか,前任者が遁走した時の場合でも ある程度何をしたかったかがわかるのでよい http://mevius.5ch.net/test/read.cgi/db/1087483786/42
43: NAME IS NULL [sage] 04/07/21 10:57 ID:??? >>42 うんうん、わかるわかる(泣)。 俺、火消し役ばっかりやらされてるんで。 でも、遁走するような奴の設計には 懐疑的になってしまうのもまた事実ですね。 でも何も無いよりいいか。 http://mevius.5ch.net/test/read.cgi/db/1087483786/43
44: NAME IS NULL [sage] 04/07/21 11:11 ID:??? 適当な設計やっても平気で提案できるくらいの奴は、 神経が図太い場合が多いから、逆に辞めない。 間違った仕様を正したいのに、認められなくて 耐えられなくなって辞める奴の方が多い。 憎まれっ子世にはばかる。 http://mevius.5ch.net/test/read.cgi/db/1087483786/44
45: NAME IS NULL [sage] 04/07/21 11:12 ID:??? 関係ないけど、火消しを入れる予算があるのなら、 最初から出火させない人を雇えよと思ったりもする。 火消し担当の人が、最初からプロジェクトに関わればいいのに。 http://mevius.5ch.net/test/read.cgi/db/1087483786/45
46: NAME IS NULL [sage] 04/07/21 11:22 ID:??? >>45 自然発火とか http://mevius.5ch.net/test/read.cgi/db/1087483786/46
上
下
前
次
1-
新
書
関
写
板
覧
索
設
栞
歴
あと 68 レスあります
スレ情報
赤レス抽出
画像レス抽出
歴の未読スレ
AAサムネイル
Google検索
Wikipedia
ぬこの手
ぬこTOP
0.005s