I'm trying to create a constraint to check that a project can have only one employee whose role is project leader but at the same time can have other employees with different roles.
My table definition:
CREATE TABLE employee
( employee_id INT NOT NULL PRIMARY KEY
,employee_role VARCHAR(15) NOT NULL
, CHECK (employee_role in ('project_leader', 'administrator', 'member'))
)
CREATE TABLE project
( project_id INT NOT NULL PRIMARY KEY
, name VARCHAR(50)
, employee_id INT NOT NULL
, employee_role VARCHAR(15) NOT NULL
, CONSTRAINT employee_project_FK
FOREIGN KEY (employee_id, employee_role)
REFERENCES employee (employee_id, employee_role)
, CONSTRAINT only_one_project_leader
CHECK (employee_role = 'project_leader')
) ;
It's unclear to me how this can be expressed in a constraint and what I need to change. If anyone would inform me what I'm doing wrong, I'd appreciate it.
You are missing a table. Your data structure wants three tables:
If you want a constraint that a project has only one leader, then you can simply add a column to Project
called ProjectLeader
. This will enforce the constraint, because there is only one slot per project for the leader. If you have to have a leader, then add in a check constraint to be sure this is not NULL
.
A sign that something is wrong with the data model is that project_id
is a primary key in the project
table. This implies that for a given project_id
, there is only one employee. I don't think that is what you intend.
EDIT:
The tables would look something like:
CREATE TABLE project
( project_id INT NOT NULL PRIMARY KEY,
name VARCHAR(50),
project_leader int references employee(employee_id)
) ;
CREATE TABLE projectemplyee
( projectemployee_id INT NOT NULL PRIMARY KEY,
project_id int references project(project_id),
employee_id int references employee(employee_id),
employee_role VARCHAR(15) NOT NULL
) ;
There is only one slot for a leader in each project. You do not need a constraint to enforce the one-ness.