如何创建国家列仅接受全称的SQL地址表？

首先，你原来的代码存在几个语法错误：

• 表创建语句应该是CREATE TABLE而非CREATE TABLES
• CASE语句不能直接嵌入列定义中，表定义仅用于声明列的结构和约束，逻辑转换需要用触发器或其他方式实现

以下是几种可行的实现方式：

方式一：用CHECK约束强制仅允许输入全称 #

直接在country列上添加CHECK约束，限定输入值必须是指定的国家全称，不符合则插入/更新失败：

CREATE TABLE addresses (
    -- 替换为你的其他列，比如id, street, city等
    id INT PRIMARY KEY AUTO_INCREMENT,
    street VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL,
    country VARCHAR(25) NOT NULL CHECK (country IN ('United Kingdom', 'United States', 'China', 'Germany'))
);

这种方式简单直接，但后续新增国家需要修改表结构的约束。

方式二：用触发器自动转换缩写为全称（并强制合法性） #

如果希望允许用户输入缩写，但自动转换为全称存储，同时确保最终存储的是合法全称，可以使用触发器：

-- 先创建基础表
CREATE TABLE addresses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    street VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL,
    country VARCHAR(25) NOT NULL
);

-- 创建插入前的触发器（以MySQL为例，不同数据库语法略有差异）
DELIMITER //
CREATE TRIGGER trg_convert_country_abbrev
BEFORE INSERT ON addresses
FOR EACH ROW
BEGIN
    -- 将缩写转换为全称
    SET NEW.country = CASE
        WHEN NEW.country = 'UK' THEN 'United Kingdom'
        WHEN NEW.country = 'US' THEN 'United States'
        WHEN NEW.country = 'CN' THEN 'China'
        WHEN NEW.country = 'DE' THEN 'Germany'
        -- 添加更多缩写映射
        ELSE NEW.country
    END;
    -- 检查转换后的值是否为合法全称，不合法则抛出错误
    IF NEW.country NOT IN ('United Kingdom', 'United States', 'China', 'Germany') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '无效的国家值，请使用全称或合法缩写';
    END IF;
END //
DELIMITER ;

同样，你可以创建BEFORE UPDATE触发器来处理更新操作的转换和校验。

方式三：用外键关联国家表（最规范的方案） #

创建独立的国家表存储所有合法的国家全称，然后通过外键约束确保addresses表的country列只能引用已存在的全称：

-- 创建国家表
CREATE TABLE countries (
    country_id INT PRIMARY KEY AUTO_INCREMENT,
    country_name VARCHAR(25) UNIQUE NOT NULL
);

-- 插入合法的国家全称
INSERT INTO countries (country_name) VALUES 
('United Kingdom'), 
('United States'), 
('China'), 
('Germany');

-- 创建地址表，通过外键关联国家表
CREATE TABLE addresses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    street VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL,
    country VARCHAR(25) NOT NULL,
    FOREIGN KEY (country) REFERENCES countries(country_name)
);

这种方式扩展性最好，后续新增国家只需向countries表插入数据，无需修改地址表结构，同时能避免输入无效值。

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