参照制約をつけたら追加/削除できなくなったでござるの巻

database teradata

事象

これはOKなケース

CREATE TABLE table1 (col1 VARCHAR(5) PRIMARY KEY);
CREATE TABLE table2 (col2 VARCHAR(5) PRIMARY KEY);
CREATE TABLE table3 (
    col3 VARCHAR(5) REFERENCES table1(col1),
    col4 VARCHAR(5) REFERENCES table2(col2),
    primary key(col3, col4)); 

INSERT INTO table1 values ('aaa');
INSERT INTO table2 values ('bbb');
INSERT INTO table3 values ('aaa', 'bbb');

これはNG

CREATE TABLE table4 (col1 VARCHAR(5) PRIMARY KEY);
CREATE TABLE table5 (col2 VARCHAR(5) PRIMARY KEY);
CREATE TABLE table6 (
    col3 VARCHAR(5) REFERENCES table1(col1) PRIMARY KEY,
    col4 VARCHAR(5) REFERENCES table2(col2)); 

INSERT INTO table4 values ('aaa');
INSERT INTO table5 values ('bbb');
INSERT INTO table6 values ('aaa', 'bbb');

5312:  Aborted due to a non-valid reference index on user_name.table6.
  • 参照索引(Reference Index = RI)が悪さしてそう。
  • どう考えても主キーの構成が関係してそう。

ここと同じ事象か？
How to validate the invalid references created [From Vireta_Godinho: Wed, 14 Jun 2006 @ 10:51 GMT]

調査結果

  • col4のRIなしで作り直しても駄目
  • col3, col4ともRIなしはOK
  • WITH CHECK / NO CHECK OPTIONを付けて作り直しても駄目。

いろいろ調べた結果、DBC.ALL_RI_Parents / DBC.ALL_RI_Childrenを見ても、table6.col4に設定したはずのRIが存在しない事に気がついた。
ちなみに、col3のRIは存在する。
また、table3の方は、col3, col4ともRIが存在する。


つまり、CREATE TABLE時に参照制約を付与する場合、主キー*1の部分キーでないと、RIを作ってくれないらしい。
多分、RIがないのにチェックして落ちてるんじゃないだろうか？


そこで、Re: How to validate the invalid references created [From Waters, Gerald: Fri, 16 Jun 2006 @ 11:09 GMT]の内容を参考に、以下のとおり試してみたら、ちゃんとRIが確認できました。

  1. CREATE TABLE時には、非主キー項目は参照制約を付与しないこと
  2. ALTER TABLEで参照制約を付与

調べてる最中に失敗した内容が見当つくと良いんだけど…。
テーブルの構造とRIは別なのかも。（テーブルをドロップしてもカスケードされない？）

追記

ALTER TABLEで参照制約を付与すると、勝手にテーブルが作られます。

*1:もしくは、PIの方が妥当か？