如何在SQL中避免员工重复分配至同一项目？

你的现有表结构没法实现员工多项目分配，更没法避免重复分配，问题出在projects表直接关联单个员工的设计上——这是一对一的关联，而员工和项目是多对多的关系，得用中间关联表来解决。

修正后的表结构设计 #

1. 员工表（employees）：保留核心属性，移除和项目绑定的日期字段（这些属于员工参与项目的动态信息，不是员工固有属性）

use projprac

create table dbo.employees
(
    [idEmployee] [int] PRIMARY KEY,
    [name] [varchar](50) NOT NULL,
    [lastname] [varchar](50) NOT NULL,
    [pesel] [int] UNIQUE NOT NULL,
    [skills] [varchar](80)
)

2. 项目表（projects）：移除错误的idPEmployee字段，改用idLeader关联员工表（项目负责人是员工的一种角色，用ID关联更规范，避免名字重复问题）

create table dbo.projects
(
    [idProject] [int] NOT NULL PRIMARY KEY,
    [topic] [varchar](70) NOT NULL,
    [budget] [money] NOT NULL,
    [start_date] [date] NOT NULL,
    [end_date] [date],
    [realization_in_procent] [int],
    [idLeader] [int] NOT NULL,

    foreign key (idLeader) references employees(idEmployee)
)

3. 员工-项目关联表（employee_projects）：这是核心，用idEmployee和idProject的复合主键强制唯一性，直接阻止重复分配；同时可以存放员工参与该项目的起止日期及其他关联信息

create table dbo.employee_projects
(
    [idEmployee] [int] NOT NULL,
    [idProject] [int] NOT NULL,
    [start_participation_date] [date] NOT NULL,
    [end_participation_date] [date],

    -- 复合主键：确保同一员工不会重复分配到同一项目
    primary key (idEmployee, idProject),
    -- 外键关联员工表和项目表
    foreign key (idEmployee) references employees(idEmployee),
    foreign key (idProject) references projects(idProject),
    -- 日期检查约束：参与结束日期必须晚于开始日期，且不能晚于当前日期
    constraint CHK_Participation_Date check (end_participation_date > start_participation_date and end_participation_date < GETDATE())
)

为什么这样能避免重复分配 #

当你尝试给同一个员工重复插入同一项目的关联记录时，数据库会因为复合主键的唯一性约束直接抛出错误，拒绝执行插入操作，从底层保证了不会出现重复分配的情况。

示例验证 #

比如先插入一条关联记录：

insert into employee_projects (idEmployee, idProject, start_participation_date)
values (1, 1, '2024-01-01')

再尝试插入完全相同的记录：

insert into employee_projects (idEmployee, idProject, start_participation_date)
values (1, 1, '2024-01-01')

数据库会报错：违反了 PRIMARY KEY 约束 'PK__employee__...'。无法在对象 'dbo.employee_projects' 中插入重复键。

内容的提问来源于stack exchange，提问作者Kacper Jóźwik