外部キーが設定されているテーブルのupdateについて

PostgreSQL8.24を利用しています。

pgADMINIIIでテーブルを作成しました。
【TABLE_A】と【TABLE_B】があります。
【TABLE_A】の【ID】が【TABLE_B】の【ID2】が外部キーとして設定してあります。

CONSTRAINT TABLE_B_fkey FOREIGN KEY (ID2)
REFERENCES TABLE_A (ID) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
上記のように記述されています。

SQL文のUPDATEを使い、IDが「01」を「05」に更新したいのですが、
以下のように記述するとエラーになります。
どのようなUPDATE文を記述すればよろしいのでしょうか。

UPDATE TABLE_A INNER JOIN TABLE_B on TABLE_A.ID=TABLE_B.ID2
SET TABLE_A.ID = '05',TABLE_B.ID2 = '05' WHERE TABLE_A.ID='01'"

よろしくお願いいたします。

No.1 ベストアンサー 回答者： chukenkenkou 回答日時： 2007/10/22 18:41

失礼ながら、いろいろな勘違いをしているようです。

update文で、複数の表を更新することはできません。あくまでも、1表だけです。
他の表の列値で更新することは可能で、サブクエリを使う方法以外に、

update 表1
set 列2=表2.列2
from 表2
where 表1.列1=表2.列1

のように、ジョインで得られた結果で更新可能ですが、更新対象はこの例では表1です。
updateでこの例のように、複数表を指定する構文を持っているRDBMSがあり、RDBMSにより構文が違う場合もあります。

>UPDATE TABLE_A INNER JOIN TABLE_B on TABLE_A.ID=TABLE_B.ID2

複数のRDBMSを知っていますが、このような構文は、見たことがありません。なぜ、このような構文を思いついたのでしょうか？

外部キー（参照制約）についても、正しく理解されていないようです。
参照制約は、以下の制約で親子間の不整合を防ぎます。これは、階層DBやネットワーク型DBの特性を、RDBMSに取り入れたものです。
親子間の不整合の発生をRDBMS側で防止してくれる一方で、オーバヘッドがあったり、運用上の注意（親の表を削除する場合、子の表を先に削除など）が必要になります。
アプリケーション側で不整合を発生させないようにできるなら、わざわざ参照制約を使う必要はありません。

表定義でのディフォルトの指定（NO ACTION）での動作は、次のようになります。

(1)子のいる親の主キーは、更新できない。
→子の外部キーを一旦、null等に更新し、子のいない状態で親の主キーを更新。その後、子の外部キーを親の主キーと同じ値に更新する。

(2)子のいる親は、削除できない。
→子を削除してから、親を削除

表定義で、「ON UPDATE CASCADE」、「ON DELETE CASCADE」を指定した場合は、次のような動作になります。

(1)親の主キーを更新すると、子の外部キーも自動的に更新。
(2)親を削除すると、子も削除。

どちらの動作をさせたいでしょうか？
それにより、表定義を変えるか、SQLの発行順などが変わってきます。

＝＝＝＝＝例１(NO ACTION)＝＝＝＝＝
１．定義
create table tbl1
(pkey char(2) primary key,
data varchar(10));
create table tbl2
(pkey int primary key,
fkey char(2),
data varchar(10),
constraint tbl1_tbl2
foreign key(fkey) references tbl1(pkey)
match simple
on update no action
on delete no action
)
;
create index tbl2idx1 on tbl2(fkey);

２．操作
(1) 子のいない親の主キー更新
update tbl1
set pkey='10'
where pkey='04';

(2) 子のいる親の主キー更新
-- 子を更新して、親との関係を一時的になくす
update tbl2
set fkey=null
where fkey='03';
-- 親を更新
update tbl1
set pkey='05'
where pkey='03';
-- 子を親に対応付け
update tbl2
set fkey=tbl1.pkey
from tbl1
where fkey is null
and tbl1.pkey='05';
または
update tbl2
set fkey='05'
from tbl1
where fkey is null;

＝＝＝＝＝例2(CASCADE)＝＝＝＝＝
１．定義
create table tbl1
(pkey char(2) primary key,
data varchar(10));
create table tbl2
(pkey int primary key,
fkey char(2),
data varchar(10),
constraint tbl1_tbl2
foreign key(fkey) references tbl1(pkey)
match simple
on update CASCADE -- no action
on delete CASCADE -- no action
)
;
create index tbl2idx1 on tbl2(fkey);

２．操作
(1)親の更新の背景で、子も更新
update tbl1
set pkey='05'
where pkey='03';

※子の表に対する操作は、SQL上はないが、RDBMSにより更新されている。

この回答へのお礼

ありがとうございます。

すみません。勉強不足で・・・。
UPDATE TABLE_A INNER JOIN TABLE_B on TABLE_A.ID=TABLE_B.ID2
に記述したのは、ACCESSのクエリーを使ったら、そのようにSQLを作成したので・・・。
表定義で、「ON UPDATE CASCADE」、「ON DELETE CASCADE」を
記述すればよいのですね。

今回はアプリケーション側で不整合を発生させないように変更いたしました。

詳しい説明本当にありがとうございました。

お礼日時：2007/10/24 10:38