MySQL 1-many relationship issue

Question

I have 2 tables, one for Employees and one for Departments. One employee may only work in ONLY one department, but a department can have multiple employees. I have added a foreign key constraint between them, but If I attempt to add the same exact employee, with the same data to the same department, the query executes instead of failing.

create table Department(id int primary key auto_increment, name varchar(100));

create table Employee(id int primary key auto_increment, 
                 department_id int not null,
                 name varchar(100),
                 foreign key (department_id) references department(id));

INSERT INTO Department(name) VALUES('China');
INSERT INTO Department(name) VALUES('England');

INSERT INTO Employee(department_id,name) VALUES (1,'Mark');
INSERT INTO Employee(department_id,name) VALUES (1,'Mark');

After inserting, for the 2nd time, Employee 'Mark', I was expecting an error but instead the query always executes. How can I restrict one specific employee to only work to one department?

Answer 1

You need to unique index on (department_id, name).

This will still allow the name mark within a other department.

ALTER TABLE `employee` ADD UNIQUE INDEX `unique_department_id__name` (`department_id`, `name`);

Query

INSERT INTO Employee(department_id,NAME) VALUES (1,'Mark');
INSERT INTO Employee(department_id,NAME) VALUES (1,'Mark');

Result

Query: INSERT INTO Employee(department_id,name) VALUES (1,'Mark')

1 row(s) affected

Execution Time : 0.013 sec
Transfer Time  : 0 sec
Total Time     : 0.014 sec
-----------------------------------------------------------

Query: INSERT INTO Employee(department_id,name) VALUES (1,'Mark')

Error Code: 1062
Duplicate entry '1-Mark' for key 'unique_department_id__name'

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.003 sec

EDITED

Query

INSERT INTO Employee(department_id,NAME) VALUES (2,'Mark');

Result

Query: INSERT INTO Employee(department_id,NAME) VALUES (2,'Mark')

1 row(s) affected

Execution Time : 0.021 sec
Transfer Time  : 0 sec
Total Time     : 0.022 sec

Answer 2

This is happening because there is no constrains on Name column.

Add unique constrain Employee.name .

eg

ALTER TABLE Employee
ADD CONSTRAINT UC_Person UNIQUE (name)

; ,