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

Question

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'"

よろしくお願いいたします。

chukenkenkou · Accepted Answer

失礼ながら、いろいろな勘違いをしているようです。

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について

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

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング