MySQL能否通过子查询限制插入？无需冗余存储关联表字段

首先明确你的核心需求：要确保同一家公司每日对同一产品只能下单一次，但这个约束需要关联User表的CompanyID，而Orders表本身没有这个字段，不想用触发器，也不想依赖应用层先查再插的逻辑（毕竟有并发风险）。

下面是几个靠谱的解决方案，按推荐程度排序：

1. 在Orders表中新增CompanyID列并创建组合唯一约束（最推荐） #

这是最直接且可靠的方案，通过在Orders表中存储用户所属的CompanyID，再结合外键保证数据一致性，最后创建组合唯一约束。

步骤如下：

第一步：给User表添加(UserID, CompanyID)的唯一约束 #

因为我们要让Orders表的外键同时关联UserID和CompanyID，确保Orders里的CompanyID和对应用户的公司一致：

ALTER TABLE User
ADD CONSTRAINT UC_User_UserID_CompanyID UNIQUE (UserID, CompanyID);

第二步：给Orders表添加CompanyID列并设置外键 #

ALTER TABLE Orders
ADD COLUMN CompanyID INT NOT NULL;

-- 外键约束确保Orders的(UserID, CompanyID)和User表的记录完全匹配
ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_User_Company
FOREIGN KEY (UserID, CompanyID)
REFERENCES User(UserID, CompanyID);

第三步：创建目标组合唯一约束 #

ALTER TABLE Orders
ADD CONSTRAINT UC_Order_Company_Date_Product
UNIQUE (CompanyID, Date, ProductID);

这样一来，你直接执行INSERT语句时，如果违反约束会抛出异常；用INSERT IGNORE（MySQL）或者ON CONFLICT DO NOTHING（PostgreSQL）就能自动跳过无效数据，完全不需要触发器，也避免了并发竞态问题。

⚠️ 注意：插入订单时需要同时指定CompanyID，不过因为有外键约束，只要UserID对应的CompanyID正确，就能成功插入，否则会报错，保证了数据的准确性。

2. 使用生成列（Generated Column）（MySQL/PostgreSQL支持） #

如果不想手动维护CompanyID列，可以用数据库的生成列功能，让数据库自动从User表获取CompanyID：

以MySQL为例，创建存储型生成列（STORED）：

ALTER TABLE Orders
ADD COLUMN CompanyID INT
GENERATED ALWAYS AS (
    SELECT u.CompanyID FROM User u WHERE u.UserID = Orders.UserID
) STORED;

-- 然后创建组合唯一约束
ALTER TABLE Orders
ADD CONSTRAINT UC_Order_Company_Date_Product UNIQUE (CompanyID, Date, ProductID);

不过这个方案有个小局限：如果User表中用户的CompanyID发生变更，Orders表的CompanyID不会自动更新（因为STORED列只在插入/更新订单时计算）。如果你的业务中用户不会变更所属公司，这个方案完全可行；如果会变更，还是推荐第一种方案。

3. 用存储过程封装插入逻辑 #

如果不想修改表结构，可以用存储过程封装插入逻辑，在存储过程内部完成校验和插入，避免应用层的并发问题：

以MySQL为例：

DELIMITER //
CREATE PROCEDURE InsertNewOrder(
    IN p_OrderID INT,
    IN p_Date DATE,
    IN p_UserID INT,
    IN p_ProductID INT
)
BEGIN
    DECLARE v_CompanyID INT;
    -- 获取用户所属公司
    SELECT CompanyID INTO v_CompanyID FROM User WHERE UserID = p_UserID;
    
    -- 校验是否存在重复订单
    IF NOT EXISTS (
        SELECT 1 FROM Orders o
        JOIN User u ON o.UserID = u.UserID
        WHERE u.CompanyID = v_CompanyID 
          AND o.Date = p_Date 
          AND o.ProductID = p_ProductID
    ) THEN
        INSERT INTO Orders(OrderID, Date, UserID, ProductID)
        VALUES(p_OrderID, p_Date, p_UserID, p_ProductID);
    END IF;
END //
DELIMITER ;

调用这个存储过程来插入订单即可，但这个方案的缺点是应用层需要适配存储过程，灵活性稍差。

为什么不推荐先SELECT再INSERT？ #

直接在应用层先查询是否存在重复，再插入的最大问题是并发竞态：当两个请求同时执行查询，都判断没有重复，然后同时插入，就会导致违反约束的情况出现，这种问题在高并发场景下很容易发生。

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