FOREIGN KEY และการดำเนินการด้านความสัมพันธ์

คีย์ต่างประเทศทำอะไร

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  total NUMERIC(10,2) NOT NULL
);

-- Inserting an order with user_id=999 fails if no such user exists.

เหตุใดจึงใช้คีย์ต่างประเทศ

การดำเนินการอ้างอิง

ON DELETE CASCADE

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL
    REFERENCES users(id) ON DELETE CASCADE,
  total NUMERIC(10,2) NOT NULL
);

ON DELETE SET NULL

CREATE TABLE employees (
  id BIGSERIAL PRIMARY KEY,
  full_name TEXT NOT NULL,
  manager_id BIGINT REFERENCES employees(id) ON DELETE SET NULL
);

ค่าเริ่มต้น: คล้าย RESTRICT

ERROR:  update or delete on table "users" violates foreign key constraint "orders_user_id_fkey" on table "orders"
DETAIL:  Key (id)=(42) is still referenced from table "orders".

สร้างดัชนีให้คอลัมน์ FK เสมอ

CREATE INDEX orders_user_id_idx ON orders(user_id);

คีย์ต่างประเทศแบบประกอบ

CREATE TABLE order_items (
  order_id BIGINT,
  line_no INT,
  PRIMARY KEY (order_id, line_no)
);
CREATE TABLE shipments (
  order_id BIGINT,
  line_no INT,
  FOREIGN KEY (order_id, line_no)
    REFERENCES order_items(order_id, line_no)
);

การเลื่อนการตรวจสอบคีย์ต่างประเทศ

ALTER TABLE orders
  ALTER CONSTRAINT orders_user_id_fkey DEFERRABLE INITIALLY DEFERRED;

BEGIN;
  INSERT INTO orders ... ;     -- FK temporarily not checked
  INSERT INTO users ... ;
COMMIT;                         -- checked here

การเพิ่มคีย์ต่างประเทศโดยไม่ปิดระบบ

ALTER TABLE orders
  ADD CONSTRAINT orders_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fkey;

ใช้การส่งต่ออย่างระมัดระวัง

สรุป

ตรวจสอบความเข้าใจอย่างรวดเร็ว