MySQL の CHECK 制約

CREATE TABLE products (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    price    DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL,

    -- Inline constraints
    CONSTRAINT chk_price    CHECK (price > 0),
    CONSTRAINT chk_quantity CHECK (quantity >= 0)
);

CREATE TABLE orders (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    status     VARCHAR(20) CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled')),
    discount   DECIMAL(5,2) CHECK (discount BETWEEN 0 AND 100)
);

CREATE TABLE shipments (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    shipped_at   DATE,
    delivered_at DATE,
    CONSTRAINT chk_dates CHECK (delivered_at IS NULL OR delivered_at >= shipped_at)
);

INSERT INTO products (name, price, quantity) VALUES ('Widget', -5.00, 10);
-- ERROR 3819 (HY000): Check constraint 'chk_price' is violated.

ALTER TABLE products
ADD CONSTRAINT chk_name_length CHECK (CHAR_LENGTH(name) >= 2);

ALTER TABLE products DROP CHECK chk_price;
-- or the SQL standard syntax:
ALTER TABLE products DROP CONSTRAINT chk_price;

SELECT cc.constraint_name, cc.check_clause
FROM information_schema.check_constraints cc
JOIN information_schema.table_constraints tc
    ON tc.constraint_schema = cc.constraint_schema
   AND tc.constraint_name   = cc.constraint_name
WHERE cc.constraint_schema = DATABASE()
  AND tc.table_name = 'products';

ALTER TABLE products ALTER CHECK chk_price NOT ENFORCED;

ALTER TABLE products ALTER CHECK chk_price ENFORCED;