MySQL多列NOT NULL表创建及两列互斥非空约束实现咨询

Hey there! Let's break down your two MySQL table creation questions with practical examples and clear explanations—no jargon overload, promise.

This one's straightforward. When defining your table columns, simply add the NOT NULL keyword to any columns that must always have a value (can't be left empty).

Here's a concrete example matching your table structure:

CREATE TABLE example_table (
    A INT NOT NULL,       -- This column can't be empty
    B VARCHAR(50) NOT NULL,  -- This one too
    C DATE,               -- Optional, can be NULL
    D TEXT,               -- Optional, can be NULL
    E DECIMAL(10,2)       -- Optional, can be NULL
);

When you try to insert or update a record without values for A or B, MySQL will throw an error—this ensures those critical columns always have data.

Your requirement here is that exactly one of C or D has a value (no both empty, no both filled). There are two solid ways to handle this depending on your MySQL version:

Option 1: Using CHECK Constraints (MySQL 8.0.16+) #

MySQL finally added full support for standard CHECK constraints in version 8.0.16, so this is the cleanest approach if you're on a recent version.

CREATE TABLE example_table (
    A INT,
    B VARCHAR(50),
    C INT,
    D VARCHAR(100),
    E DECIMAL(10,2),
    -- Enforce exactly one of C or D has a value
    CONSTRAINT chk_c_d_exclusive CHECK (
        (C IS NOT NULL AND D IS NULL) 
        OR 
        (C IS NULL AND D IS NOT NULL)
    )
);

This constraint explicitly checks two scenarios: either C has data and D is empty, or D has data and C is empty. Any attempt to insert/update a record that violates this (both empty or both filled) will fail with an error.

Option 2: Using Triggers (Older MySQL Versions) #

If you're stuck on a MySQL version before 8.0.16 (where CHECK constraints are ignored), triggers are the way to go. We'll create triggers to validate the C/D rule before any insert or update.

First, create the table:

CREATE TABLE example_table (
    A INT,
    B VARCHAR(50),
    C INT,
    D VARCHAR(100),
    E DECIMAL(10,2)
);

Then add the triggers:

-- Trigger for INSERT operations
DELIMITER //
CREATE TRIGGER trg_check_c_d_insert BEFORE INSERT ON example_table
FOR EACH ROW
BEGIN
    IF (NEW.C IS NOT NULL AND NEW.D IS NOT NULL) OR (NEW.C IS NULL AND NEW.D IS NULL) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error: Exactly one of columns C or D must contain a value.';
    END IF;
END //
DELIMITER ;

-- Trigger for UPDATE operations
DELIMITER //
CREATE TRIGGER trg_check_c_d_update BEFORE UPDATE ON example_table
FOR EACH ROW
BEGIN
    IF (NEW.C IS NOT NULL AND NEW.D IS NOT NULL) OR (NEW.C IS NULL AND NEW.D IS NULL) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error: Exactly one of columns C or D must contain a value.';
    END IF;
END //
DELIMITER ;

These triggers run right before inserting or updating a record. If the C/D values don't meet your rule, they'll throw a custom error message and block the operation.

内容的提问来源于stack exchange，提问作者Pejman