数据库层级验证需求：多用户环境下URL唯一性约束设计

基础表结构与关联约束 #

首先要确保表间关联的完整性，避免出现非法的跨用户关联数据：

1. 外键约束配置 #

• Environments表：添加外键约束，确保user_id指向合法的用户

  ALTER TABLE Environments
  ADD CONSTRAINT fk_environments_user FOREIGN KEY (user_id) REFERENCES Users(id);
  

• Websites表：添加双外键约束，分别关联用户和环境

  ALTER TABLE Websites
  ADD CONSTRAINT fk_websites_user FOREIGN KEY (user_id) REFERENCES Users(id),
  ADD CONSTRAINT fk_websites_environment FOREIGN KEY (environment_id) REFERENCES Environments(id);
  

2. 确保Website与所属环境的用户一致性 #

为避免出现"Website的user_id和其关联环境的user_id不匹配"的非法数据，需添加额外约束：

方案A：复合外键约束（推荐，无触发器依赖）

先给Environments表创建(user_id, id)的唯一索引，再给Websites表添加复合外键：

-- 给Environments表创建唯一索引，支持复合外键关联
CREATE UNIQUE INDEX idx_environments_user_id ON Environments(user_id, id);

-- 给Websites表添加复合外键，确保environment_id对应的环境属于当前user_id
ALTER TABLE Websites
ADD CONSTRAINT fk_websites_user_environment FOREIGN KEY (user_id, environment_id) REFERENCES Environments(user_id, id);

方案B：检查约束（部分数据库支持，如PostgreSQL、MySQL 8.0.16+）

ALTER TABLE Websites
ADD CONSTRAINT chk_websites_user_environment_match CHECK (
  user_id = (SELECT user_id FROM Environments WHERE id = environment_id)
);

实现URL唯一性约束 #

1. 同一环境内URL唯一 #

给Websites表创建(environment_id, url)的复合唯一索引，确保同一环境下不会出现重复URL：

CREATE UNIQUE INDEX idx_websites_environment_url ON Websites(environment_id, url);

2. 全局URL唯一 #

直接给Websites表的url字段添加唯一约束，确保整个系统中URL只能被一个用户拥有：

ALTER TABLE Websites
ADD CONSTRAINT uq_websites_url UNIQUE (url);

验证逻辑说明 #

• 当尝试在同一环境下创建重复URL时，idx_websites_environment_url会触发唯一约束冲突，阻止操作；
• 当尝试创建已被其他用户使用的URL时，uq_websites_url会触发全局唯一约束冲突，阻止操作；
• 复合外键/检查约束确保了Website的用户与所属环境的用户一致，避免了跨用户的非法关联数据。

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