implementing UNIQUE across linked tables in MySQL

Question

a USER is a PERSON and a PERSON has a COMPANY - user -> person is one-to-one, person -> company is many-to-one.

person_id is FK in USER table. company_id is FK in PERSON table.

A PERSON may not be a USER, but a USER is always a PERSON.

If company_id was in user table, I could create a unique key based on username and company_id, but it isn't, and would be a duplication of data if it was.

Currently, I'm implementing the unique username/company ID rule in the RoseDB manager wrapper code, but it feels wrong. I'd like to define the unique rule at the DB level if I can, but I'm not sure excactly how to approach it. I tried something like this:

alter table user add unique(used_id,person.company_id);

but that doesn't work.

By reading through the documentation, I can't find an example that does anything even remotely similar. Am I trying to add functionality that doesn't exist, or am I missing something here?

Answer 1

Well, there's nothing simple that does what you want. You can probably enforce the constraint you need using BEFORE INSERT and BEFORE UPDATE triggers, though. See this SO question about raising MySQL errors for how to handle making the triggers fail.

Answer 2

You could define the UNIQUE constraint in the Person table:

CREATE TABLE Company (
 company_id SERIAL PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE Person (
 person_id SERIAL PRIMARY KEY,
 company_id BIGINT UNSIGNED,
 UNIQUE KEY (person_id, company_id),
 FOREIGN KEY (company_id) REFERENCES Company (company_id)
) ENGINE=InnoDB;

CREATE TABLE User (
 person_id BIGINT UNSIGNED PRIMARY KEY,
 FOREIGN KEY (person_id) REFERENCES Person (person_id)
) ENGINE=InnoDB;

But actually you don't need the unique constraint even in the Person table, because person_id is already unique on its own. There's no way a given person_id could reference two companies.

So I'm not sure what problem you're trying to solve.

---

Re your comment:

That doesn't solve the issue of allowing the same username to exist in different companies.

So you want a given username to be unique within one company, but usable in different companies? That was not clear to me from your original question.

So if you don't have many other attributes specific to users, I'd combine User with Person and add an "is_user" column. Or just rely on it being implicitly true that a Person with a non-null cryptpass is by definition a User.

Then your problem with cross-table UNIQUE constraints goes away.

Answer 3

Are there more attributes to your PERSON table? Reason I ask is that what you want to implement is a typical corollary table:

USERS table:

• user_id (pk)

USER_COMPANY_XREF (nee PERSON) table:

• user_id (pk, fk)
• company_id (pk, fk)
• EFFECTIVE_DATE (not null)
• EXPIRY_DATE (not null)

COMPANIES table:

• company_id (pk)

The primary key of the USER_COMPANY_XREF table being a composite key of USERS.user_id and COMPANIES.company_id would allow you to associate a user with more than one company while not duplicating data in the USERS table, and provide referencial integrity.