Store the same Unique Number for different users in MySql

Question

I have a Employee Table that stores unique Employee numbers in the emp_no column. The thing is if another user or company stores its Employees in the same table and have an employee with the same number e.g. 1001 that has already been used they cannot store there employee.
WHAT I want to know is should I create a new employee table for each new user or is there a different solution?

you can make it unique with the company id and emp_no. So there will be an employ id under a company only one time. For batter answer, you can include your Employee table screen sort — Shakil Hossain, Nov 09 '19 at 06:54
Make the primary key be a combination of company ID and EmployeeID. A key can be made up of one or more columns — ADyson, Nov 09 '19 at 09:45

Xavier FRANCOIS · Accepted Answer · 2019-11-09T08:30:38.193

2

No, don't create multiple employees table

Your case is very standard in the SQL world, it's what we call a 1 to N relationship (or one to many). A company can have many employees but an employee is linked to one company.

You need to create a company table with a unique company id and a company name. Then, in your employee table, you add a new column with a foreign key constraint to the company id column. Finally, in your employee table, set the unique constraint on the couple (emp_no, company_id) so that the unicity constraint represents your real world constraint.

edited Nov 09 '19 at 08:30

answered Nov 09 '19 at 07:06

Xavier FRANCOIS

652
4
15

Thank you for your answer. Just to clarify so that I do not make an error with my implementation: 1] Company tb with your parameters (have that already); 2] Foreign key constraint on child employee table ```emp_entity id column``` (have that as on cascade update) 3] Unique constraint on two columns ```emp_number``` and ```emp_entity``` (do not have this yet). Therefore will the edit on my table look as follows: ```ALTER TABLE `database`.`employeetable` DROP INDEX `unique_emptb_empno`, ADD UNIQUE `unique_emptb_empno` (`emp_number`, `emp_entity`) USING BTREE;``` – Hmerman6006 Nov 09 '19 at 12:32
I tested your answer and it works great! Thanks again. – Hmerman6006 Nov 09 '19 at 12:44

score 1 · Answer 2 · answered Nov 09 '19 at 12:29

Baxbong's answer is correct, but it doesn't quite go far enough. You should set up the tables like this:

create table companies (
    company_id int auto_increment primary key,
    company_name varchar(255),
    . . .  -- all your other columns
);

create table employees (
    employee_id int auto_increment primary key,
    company_id int not null,
    emp_no varchar(255) not null,
    . . .  -- all your other columns
    constraint unq_employees_company_empno unique (company_id, emp_no),
    constraint fk_employees_company foreign key (company_id) references companies (company_id)
);

The important point here is that employees has a single primary key column. This can be used for foreign key references in other tables.

Store the same Unique Number for different users in MySql

2 Answers2