SQL创建Transit表分类列，无需插入参考表限定取值为Plane/Car/Truck

你可以通过以下两种不需要额外创建参考表、插入参考值的方案实现约束： #

方案1：使用CHECK约束（符合SQL标准，兼容性最优） #

直接在Transit表定义中添加CHECK约束限制method的取值范围即可，你可以根据业务需要选择存储字符串形式的运输方式名，或者数字编码：

存储运输方式名称（可读性更高） #

CREATE TABLE Transit (
    package VARCHAR(10) NOT NULL,
    departDistributionCentre VARCHAR(100) NOT NULL, 
    departTimestamp TIMESTAMP NOT NULL,
    arriveDistributionCentre VARCHAR(100) NOT NULL, 
    arriveTimestamp TIMESTAMP NOT NULL, 
    method VARCHAR(10) NOT NULL,
    cost INT,
    PRIMARY KEY (package, departDistributionCentre, departTimestamp, arriveDistributionCentre, arriveTimestamp),
    -- 新增CHECK约束限制运输方式取值
    CONSTRAINT method_check CHECK (method IN ('Plane', 'Car', 'Truck')),
    CONSTRAINT depart_fk FOREIGN KEY (package, departDistributionCentre, departTimestamp) REFERENCES Depart(package, distributionCentre, timestamp) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT arrive_fk FOREIGN KEY (package, arriveDistributionCentre, arriveTimestamp) REFERENCES Arrive(package, distributionCentre, timestamp) ON DELETE RESTRICT ON UPDATE CASCADE
);

存储数字编码（占用存储空间更小） #

如果需要保留原有的数字编码存储逻辑，也可以直接对数字范围做约束：

CREATE TABLE Transit (
    package VARCHAR(10) NOT NULL,
    departDistributionCentre VARCHAR(100) NOT NULL, 
    departTimestamp TIMESTAMP NOT NULL,
    arriveDistributionCentre VARCHAR(100) NOT NULL, 
    arriveTimestamp TIMESTAMP NOT NULL, 
    -- 1=Plane, 2=Car, 3=Truck
    method INT NOT NULL,
    cost INT,
    PRIMARY KEY (package, departDistributionCentre, departTimestamp, arriveDistributionCentre, arriveTimestamp),
    CONSTRAINT method_check CHECK (method IN (1,2,3)),
    CONSTRAINT depart_fk FOREIGN KEY (package, departDistributionCentre, departTimestamp) REFERENCES Depart(package, distributionCentre, timestamp) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT arrive_fk FOREIGN KEY (package, arriveDistributionCentre, arriveTimestamp) REFERENCES Arrive(package, distributionCentre, timestamp) ON DELETE RESTRICT ON UPDATE CASCADE
);

方案2：使用ENUM枚举类型（适配支持ENUM的数据库） #

对于MySQL等原生支持ENUM类型的数据库，可以直接将method列定义为枚举类型，天然限制取值范围：

CREATE TABLE Transit (
    package VARCHAR(10) NOT NULL,
    departDistributionCentre VARCHAR(100) NOT NULL, 
    departTimestamp TIMESTAMP NOT NULL,
    arriveDistributionCentre VARCHAR(100) NOT NULL, 
    arriveTimestamp TIMESTAMP NOT NULL, 
    method ENUM('Plane', 'Car', 'Truck') NOT NULL,
    cost INT,
    PRIMARY KEY (package, departDistributionCentre, departTimestamp, arriveDistributionCentre, arriveTimestamp),
    CONSTRAINT depart_fk FOREIGN KEY (package, departDistributionCentre, departTimestamp) REFERENCES Depart(package, distributionCentre, timestamp) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT arrive_fk FOREIGN KEY (package, arriveDistributionCentre, arriveTimestamp) REFERENCES Arrive(package, distributionCentre, timestamp) ON DELETE RESTRICT ON UPDATE CASCADE
);

注意：8.0.16之前的MySQL版本对CHECK约束支持不完善，该场景下优先选择ENUM方案；PostgreSQL、SQL Server等其他数据库可以直接使用CHECK约束。

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