主キーと外部キー（表どうしを結び付ける）の使い方

REFERENCES の右に書くのは指し先です。指し先の列には主キーか一意制約が必要で、ただの列は指せません。指した先が 1 行に決まらないと、実在するかを確かめようがないためです。

CREATE TABLE keihi (
  keihi_id int PRIMARY KEY,
  busho_id int NOT NULL REFERENCES busho(busho_id),
  kingaku  int NOT NULL
);

INSERT INTO keihi (keihi_id, busho_id, kingaku) VALUES (1, 99, 30000);

「violates foreign key constraint」で落ちます。busho に 99 番の部署が無いためです。入る前に止まるので、あとから辻褄合わせをしなくて済みます。

CREATE TABLE keihi (
  keihi_id int PRIMARY KEY,
  busho_id int NOT NULL REFERENCES busho(busho_name),
  kingaku  int NOT NULL
);

「foreign key constraint "keihi_busho_id_fkey" cannot be implemented」＝この外部キーは作れないと出ます。busho_name には UNIQUE が付いていないので、同じ名前の部署が 2 つあったときに指し先が決まりません。指し先には主キーか一意制約が要る、というのはこのためです。

指し先と、親を消したときにどうなるかが定義として残ります。実際に消してみなくても、この表がどういう約束になっているかを読めます。

CREATE TABLE bunrui (
  bunrui_id int PRIMARY KEY,
  nm        text NOT NULL
);

CREATE TABLE memo (
  memo_id   int PRIMARY KEY,
  bunrui_id int REFERENCES bunrui(bunrui_id) ON DELETE CASCADE,
  honbun    text NOT NULL
);

INSERT INTO bunrui (bunrui_id, nm) VALUES (1, 'renraku'), (2, 'gijiroku');
INSERT INTO memo (memo_id, bunrui_id, honbun) VALUES (1, 1, 'いち'), (2, 2, 'に');

DELETE FROM bunrui WHERE bunrui_id = 1;

SELECT memo_id, bunrui_id, honbun FROM memo ORDER BY memo_id;

分類 1 を消しただけで、メモの 1 行目まで消えています。DELETE FROM memo は一度も書いていません。

⚠️ この書き方は Oracle Database では使えません（text 型の列）。製品ごとの対応表

CREATE TABLE bunrui (
  bunrui_id int PRIMARY KEY,
  nm        text NOT NULL
);

CREATE TABLE memo (
  memo_id   int PRIMARY KEY,
  bunrui_id int REFERENCES bunrui(bunrui_id) ON DELETE SET NULL,
  honbun    text NOT NULL
);

INSERT INTO bunrui (bunrui_id, nm) VALUES (1, 'renraku'), (2, 'gijiroku');
INSERT INTO memo (memo_id, bunrui_id, honbun) VALUES (1, 1, 'いち'), (2, 2, 'に');

DELETE FROM bunrui WHERE bunrui_id = 1;

SELECT memo_id, bunrui_id, honbun FROM memo ORDER BY memo_id;

1 行目のメモは消えずに残り、bunrui_id だけが NULL になりました。本文は失われていません。

⚠️ この書き方は Oracle Database では使えません（text 型の列）。製品ごとの対応表

CREATE TABLE bunrui (
  bunrui_id int PRIMARY KEY,
  nm        text NOT NULL
);

CREATE TABLE memo (
  memo_id   int PRIMARY KEY,
  bunrui_id int NOT NULL REFERENCES bunrui(bunrui_id) ON DELETE SET NULL,
  honbun    text NOT NULL
);

INSERT INTO bunrui (bunrui_id, nm) VALUES (1, 'renraku');
INSERT INTO memo (memo_id, bunrui_id, honbun) VALUES (1, 1, 'いち');

DELETE FROM bunrui WHERE bunrui_id = 1;

表を作るところは通ってしまい、親を消そうとして初めて落ちます。書けたことと、破綻しないことは別です。

⚠️ この書き方は Oracle Database では使えません（text 型の列）。製品ごとの対応表