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 ididEmployee
(also Primary Key for that table)Projects
has one row per project,with ididProject
(also Primary Key)Project
currently has a fieldidEmployee
which is a foreign key toEmployees
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.