How to prevent entering the same value second time in sql?

November 29, 2022

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)
)

>Solution :

A unique constraint over the columns involved will prevent duplicate entries. Put a unique constraint on table dbo.projekty with columns idProjektu and idPracownika. Then inserting a record for an employee for a project already assigned will result in a duplicate and violate the unique constraint.