I have the following problem: I have to create table with projects and employees. Employee can be assigned to few projects, but he cannot be assigned twice to the same projects. How can I prevent doing that?

My tables:

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,
    [start_project_date][date],
    [end_project_date][date],
    [skills][varchar](80),

    constraint CHK_Date check (end_project_date > start_project_date and end_project_date < GetDate())
)

create table dbo.projects
(
    [idProject][int] NOT NULL,
    [topic][varchar](70) NOT NULL,
    [budget][money] NOT NULL,
    [start_date][date] NOT NULL,
    [end_date][date],
    [realization_in_procent][int],
    [leader][varchar](50) NOT NULL,
    [idPEmployee][int],

    primary key (idProject),
    foreign key (idPEmployee) references employees(idPEmployee)
)

CodePudding user response：

Just confirming my reading and summarising before I start

• Employees table has one row per employee, and each employee gets an id idEmployee (also Primary Key for that table)
• Projects has one row per project,with id idProject (also Primary Key)
• Project currently has a field idEmployee which is a foreign key to Employees

Now, the question you asked was "Employee can be assigned to few projects but he cannot be assigned twice to the same projects. How can I prevent doing that?"

I think there is an issue with the current question - because at the moment only one employee can be assigned to each project. Therefore, an employee cannot be assigned twice to the same project, because only one employee at any time can be assigned to the project. Therefore your structure already prevents this.

I assume, therefore, you're asking one of two questions

• How can we prevent an employee being assigned to multiple projects? or
• How do I change the structure so that multiple employees can be assigned to a given project, but stop the same employee being assigned twice to that project?

I'll answer each of these in turn

How to prevent an employee being assigned to multiple projects

The initial temptation is to do a unique constraint in the Project table, on idEmployee. However, there is an issue with NULLs. Unique constraints don't allow multiple NULLs - meaning projects will always need to have an idEmployee applied.

Instead, use a filtered unique index e.g.,

CREATE UNIQUE NONCLUSTERED INDEX IX_Project_Employee 
ON dbo.Project
    (idEmployee) 
WHERE (idEmployee IS NOT NULL);

This acts similarly to a constraint, but only on records where idEmployee is not NULL.

Edit: Another approach to do this is simply to put idProject in the Employees table, so each employee can only have one Project (and removing idEmployee from the Project table). However, this means multiple employees can be assigned to the same Project.

Allowing multiple employees on the same project

To do this, you need a many-to-many table between Project and Employee. If you add a primary key covering the two ID fields, then it automatically won't allow duplicates.

CREATE TABLE project_employee (
    idProject int,
    idEmployee int,
    PRIMARY KEY (idProject, idEmployee)
    )

Then remove the idEmployee field from the Project table - and instead put all the relevant employees in this new many-to-many table.