CODEWITHSUNDEEP
mysqlsqlunique-constraint
Hmerman6006
Hmerman6006

Reputation: 1913

Store the same Unique Number for different users in MySql

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?

Upvotes: 0

Views: 191

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

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.

Upvotes: 1

Xavier FRANCOIS
Xavier FRANCOIS

Reputation: 712

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.

Upvotes: 2

Related Questions