Zenn
Open2

DBのチェック制約、railsでの付与の方法について

MySQL
check
おっちー(O.S)おっちー(O.S)

具体例

単一のカラムにCHECK制約をつける

CREATE TABLE users
(
  id INT PRIMARY KEY AUTO_INCREMENT,
  age INT CHECK (0 < age AND age <= 200)
);

複数のカラムに跨いでCHECK制約をつける

CREATE TABLE addresses
(
  id INT PRIMARY KEY AUTO_INCREMENT,
  prefecture VARCHAR(4) NOT NULL,
  city_town_village VARCHAR(10),
  ward VARCHAR(10),
  CONSTRAINT ctv_ward_blank CHECK
  (
    (prefecture = '東京都' AND city_town_village IS NULL AND ward IS NOT NULL) OR
    (prefecture = '東京都' AND city_town_village IS NOT NULL AND ward IS NULL) OR
    (prefecture <> '東京都' AND city_town_village IS NOT NULL)
  )
);