Deploying UNIQUE through linked tables in MySQL

Question

The USER is HUMAN, and HUMAN has a COMPANY - user → person individually, person → company many-to-one.

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

A MAN may not be a USER, but the USER is always HUMAN.

If company_id was in the user table, I could create a unique key based on the username and company_id, but this is not so, and it will be data duplication if it were.

I am currently using a unique username / company ID rule in the RoseDB manager shell code, but it doesn’t feel right. I would like to define a unique rule at the database level if I can, but I'm not sure how it fits into it. I tried something like this:

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

but it does not work.

After reading the documentation, I can not find an example that does something even remotely similar. Am I trying to add functionality that does not exist, or am I missing something here?

Answer 1

Well, there is nothing simple that does what you want. You can probably use a limitation that you need, using BEFORE INSERTand BEFORE UPDATE triggers . See this question on raising MySQL errors for how to deal with trigger errors.

Answer 2

You can define the restriction UNIQUEin the table Person:

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;

Person, person_id . , person_id .

, .

---

:

.

, , , ? .

, , , User with Person "is_user". , , Person .

UNIQUE .

Answer 3

PERSON ? , , , , , :

USERS :

• user_id (pk)

USER_COMPANY_XREF ( nee PERSON):

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

COMPANIES :

• company_id (pk)

USER_COMPANY_XREF, USERS.user_id COMPANIES.company_id, , USERS .