Implementing Unique Across Linked Tables In Mysql

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 tabl

Solution 1:

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

Solution 2:

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.

Solution 3:

You could define the UNIQUE constraint in the Person table:

CREATETABLE Company (
 company_id SERIAL PRIMARY KEY
) ENGINE=InnoDB;

CREATETABLE 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;

CREATETABLEUser (
 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.