MS SQL Server中单主键表与复合主键表的外键创建问题

一、当然能创建外键！ #

你搞错外键的逻辑啦——外键只需要让子表的某个字段（或字段组合）匹配父表的主键就行，完全不需要管子表自己的主键是不是复合的。这里只需要在product_price_history的product_id字段上建外键，关联products的product_id主键，和它自身是复合主键根本不冲突。

创建外键的代码：

ALTER TABLE product_price_history
ADD CONSTRAINT fk_product_price_history_product
FOREIGN KEY (product_id) REFERENCES products(product_id)
-- 下面两行是可选配置，根据你的业务需求调整：
-- ON DELETE CASCADE -- 删除产品时同步删除其价格历史
-- ON UPDATE CASCADE;

二、最优方案（完美匹配你的两个需求） #

需求1：确保同一产品同一时间只有一个价格 #

你已经把(product_id, start_date)设为复合主键，这已经保证了同一产品在同一个生效日期下只能有一条价格记录。但要彻底实现“任意时间点同一产品只有一个有效价格”（也就是价格时间区间不能重叠），还需要两步：

1. 先保证日期区间的合法性：

ALTER TABLE product_price_history
ADD CONSTRAINT chk_price_date_range
CHECK (end_date >= start_date OR end_date IS NULL);

2. 防止同一产品的价格区间重叠：
   可以用触发器来检查，每次插入或更新时验证新记录和已有记录的时间区间是否冲突：

CREATE TRIGGER trg_check_price_overlap
ON product_price_history
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT 1
        FROM inserted i
        JOIN product_price_history p
            ON i.product_id = p.product_id
            AND i.start_date <> p.start_date -- 排除当前操作的记录本身
            AND (
                -- 新记录的生效日期落在已有记录的区间内
                (i.start_date BETWEEN p.start_date AND ISNULL(p.end_date, GETDATE()))
                -- 新记录的结束日期落在已有记录的区间内
                OR (ISNULL(i.end_date, GETDATE()) BETWEEN p.start_date AND ISNULL(p.end_date, GETDATE()))
                -- 已有记录的生效日期落在新记录的区间内
                OR (p.start_date BETWEEN i.start_date AND ISNULL(i.end_date, GETDATE()))
            )
    )
    BEGIN
        RAISERROR('同一产品的价格时间区间不能重叠', 16, 1);
        ROLLBACK TRANSACTION;
    END
END

另外，如果你需要保证同一产品只有一条“当前生效”的价格（即end_date为空的记录），可以加个唯一索引：

CREATE UNIQUE NONCLUSTERED INDEX idx_unique_active_price
ON product_price_history (product_id)
WHERE end_date IS NULL;

需求2：建立逻辑关联用于数据库图和关联查询 #

上面创建的外键fk_product_price_history_product直接就能满足：数据库图里会自动显示products和product_price_history的一对多关联，关联查询时直接用product_id关联即可，比如：

SELECT p.product_name, ph.start_date, ph.end_date, ph.price
FROM products p
JOIN product_price_history ph ON p.product_id = ph.product_id
WHERE p.product_name = '无线耳机';

三、外键非常有必要建 #

• 保证数据干净：杜绝出现不存在的产品ID对应的价格记录，避免脏数据；
• 逻辑一目了然：数据库图里的关联关系能直观体现业务逻辑，别人看库结构也能快速明白；
• 查询更靠谱：数据库会利用外键索引优化关联查询的性能，同时避免关联到无效数据。

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