MySQL / Foreign key

Foreign Key

Foreign Key бол нэг хүснэгтийн баганыг нөгөө хүснэгтийн PRIMARY KEY-тэй холбодог constraint юм. Хүснэгтүүдийн хоорондын харилцааны бүрэн бүтэн байдал (referential integrity)-ийг хангана.

Яагаад Foreign Key хэрэгтэй вэ?

Foreign Key-гүй бол дараах алдаа гарч болно:

sql
-- users хүснэгтэд id=99 гэж байхгүй ч...
INSERT INTO orders (user_id, product_id) VALUES (99, 1);
-- Амжилттай орно — хаана ч байхгүй хэрэглэгчийн захиалга үүснэ!

Foreign Key байвал MySQL өөрөө хянана — users хүснэгтэд id=99 байхгүй бол INSERT блоклогдоно.

Foreign Key тодорхойлох

sql
CREATE TABLE orders (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT NOT NULL,
  product_id INT NOT NULL,
  quantity   INT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id)    REFERENCES users(id),

  CONSTRAINT fk_orders_product
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Синтакс:

sql
CONSTRAINT constraint_нэр
  FOREIGN KEY (энэ_хүснэгтийн_багана)
  REFERENCES заагдсан_хүснэгт(заагдсан_багана)

Referential Integrity — бүрэн бүтэн байдал

Foreign Key тохируулсны дараа MySQL дараах дүрмүүдийг автоматаар хэрэгжүүлнэ:

1. Оруулах үед шалгалт:

sql
-- users хүснэгтэд id=999 байхгүй бол
INSERT INTO orders (user_id, product_id) VALUES (999, 1);
-- ERROR 1452: Cannot add or update a child row: a foreign key constraint fails

2. Устгах үед шалгалт:

sql
-- orders хүснэгтэд user_id=1 байгаа бол
DELETE FROM users WHERE id = 1;
-- ERROR 1451: Cannot delete or update a parent row: a foreign key constraint fails

ON DELETE — устгах үеийн үйлдэл

Эцэг хүснэгтийн мөр устгагдах үед холбоотой хүснэгтэд ямар үйлдэл хийхийг заана.

RESTRICT (анхдагч)

Холбоотой мөр байгаа тул устгахыг хориглоно:

sql
CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE RESTRICT
sql
DELETE FROM users WHERE id = 1;
-- ERROR 1451: Cannot delete or update a parent row

CASCADE

Эцэг мөр устгагдвал холбоотой мөрүүдийг дагуулан устгана:

sql
CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE CASCADE
sql
DELETE FROM users WHERE id = 1;
-- users дэх id=1 мөр устгагдана
-- orders дэх user_id=1 бүх мөр автоматаар устгагдана ✓

SET NULL

Эцэг мөр устгагдвал хүүхэд хүснэгтийн Foreign Key баганыг NULL болгоно:

sql
CONSTRAINT fk_orders_courier
  FOREIGN KEY (courier_id) REFERENCES couriers(id)
  ON DELETE SET NULL

Энэ тохиолдолд courier_id багана NULL байж болохуйц тодорхойлогдсон байх ёстой.

ON UPDATE — шинэчлэх үеийн үйлдэл

Эцэг хүснэгтийн PRIMARY KEY өөрчлөгдвөх үед хэрэглэгдэнэ. CASCADE бол хамгийн түгээмэл:

sql
CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE CASCADE
  ON UPDATE CASCADE

Хэзээ ямар тохиргоо ашиглах вэ?

| Нөхцөл | Зөвлөмж | | ------------------------------------------------ | -------------------- | | Хэрэглэгч устгагдвал захиалга ч устгагдах ёстой | ON DELETE CASCADE | | Хэрэглэгч устгагдсан ч захиалгын бичлэг хадгалах | ON DELETE SET NULL | | Холбоотой өгөгдөл байгаа бол устгахыг хориглох | ON DELETE RESTRICT |

Index автоматаар үүсдэг

MySQL нь Foreign Key баганад автоматаар index үүсгэнэ — JOIN болон шалгалтын хурдыг сайжруулахын тулд. Энийг шалгах:

sql
SHOW INDEX FROM orders;
код
+--------+------------+---------------------+...+-------------+
| Table  | Key_name   | Column_name         |...| Index_type  |
+--------+------------+---------------------+...+-------------+
| orders | PRIMARY    | id                  |...| BTREE       |
| orders | fk_orders_user    | user_id      |...| BTREE       |
| orders | fk_orders_product | product_id   |...| BTREE       |
+--------+------------+---------------------+...+-------------+

ALTER TABLE-д Foreign Key нэмэх

sql
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE;

Foreign Key устгах

sql
ALTER TABLE orders
  DROP FOREIGN KEY fk_orders_user;

Байгаа Foreign Key-үүдийг харах

sql
SELECT
  CONSTRAINT_NAME,
  TABLE_NAME,
  COLUMN_NAME,
  REFERENCED_TABLE_NAME,
  REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND REFERENCED_TABLE_NAME IS NOT NULL;

Жишээ: myshop-ийн бүрэн схем

sql
CREATE TABLE users (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(255) NOT NULL,
  created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT uq_users_email UNIQUE (email)
);

CREATE TABLE products (
  id         INT            AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(200)   NOT NULL,
  price      DECIMAL(10,2)  NOT NULL,
  stock      INT            DEFAULT 0,
  CONSTRAINT chk_price CHECK (price > 0),
  CONSTRAINT chk_stock CHECK (stock >= 0)
);

CREATE TABLE orders (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT NOT NULL,
  product_id INT NOT NULL,
  quantity   INT NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE CASCADE,
  CONSTRAINT fk_orders_product
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
  CONSTRAINT chk_quantity CHECK (quantity > 0)
);

Одоо:

  • users дэх хэрэглэгч устгагдвал тэдний захиалгууд автоматаар устгагдана (CASCADE)
  • products дэх бараа устгахыг оролдвол захиалга байгаа тул хориглогдоно (RESTRICT)

Дараагийн хичээлд:

INNER JOIN — хоёр ба түүнээс дээш хүснэгтийг нэгтгэж query хийх, table alias ашиглах аргуудыг үзнэ.

← Өмнөх хичээлConstraint (PRIMARY KEY, NOT NULL)Дараах хичээл →INNER JOIN