How to enforce foreign key constraint from unrelated tables in Mysql?

Question

this is my DB structure.

this is the script I used to create the tables

use for_stkoverflow;

CREATE TABLE UserGroup (
    groupid MEDIUMINT NOT NULL AUTO_INCREMENT,
    groupname VARCHAR(100),
PRIMARY KEY (`groupid`)
);

CREATE TABLE User_det (
    Usrid MEDIUMINT NOT NULL AUTO_INCREMENT,
    usrname VARCHAR(255),
    groupid MEDIUMINT,
PRIMARY KEY (`Usrid`),
    Foreign Key (groupid)
        references UserGroup (groupid)
);

CREATE TABLE Accounts (
    acid MEDIUMINT NOT NULL AUTO_INCREMENT,
    groupid MEDIUMINT,
acname VARCHAR(255),
PRIMARY KEY (`acid`),
    Foreign Key (groupid)
        references UserGroup (groupid)
);

create table Ledger (
    ledgerid MEDIUMINT NOT NULL AUTO_INCREMENT,
ledgername VARCHAR(255),
    acid mediumint,
Usrid mediumint,
PRIMARY KEY (ledgerid),
    Foreign Key (acid)
        references Accounts (acid),
Foreign Key (Usrid)
        references User_det (Usrid)
);

I have the following data entered

UserGroup

----------
- groupid groupname
--------------------
- 1   Group1
- 2   Group2

User_det

--------
- Usrid usrname groupid
-----------------------
- 1     User1       1
- 2     User2       2

Accounts

--------
- acid groupid acname
---------------------
- 1      1      ac1
- 2      2      ac2

Ledger

--------
-ledgerid ledgername acid Usrid
--------------------------------
- 1         ledger1 1   1
- 2         ledger2 2   2
- 3         ledger3 1   2
- 4         ledger4 2   1

SELECT t1.ledgerid, t1.ledgername,t2.acname,t3.usrname
  FROM Ledger AS t1 
INNER JOIN Accounts AS t2 ON t1.acid = t2.acid
Inner join User_det AS t3 ON t1.Usrid = t3.Usrid;

The current table structure permits insertion of data that violates DB integrity.

The entry ledgerid 3 is invalid ---------------------------------- because acname ac1 belongs to group1 to which User2 is not part of. The entry ledgerid 4 is

invalid

because acname ac2 belongs to group2 to which User1 is not part of.

How can I prevent the insert of such data?

Right now in the application I am doing this check via PHP in the BL layer.

Can I enforce this at the DB level because I do some import from the backed also without using the PHP front end.

Answer 1

Use identifying relationships, similar to this:

Note how UserGroup PK migrates from the top of this "diamond", down both "sides" and merges at the "bottom". Since a row at the bottom contains only one field identifying the top, it cannot be related to multiple rows at the top.

You can still keep your other keys if you need them for other purposes and/or make the keys above alternate (i.e. UNIQUE constraints)...

---

BTW, use naming more consistently - I'd recommend always using singular and prefixing PK fields with unabbreviated table names...

Answer 2

Side note :I believe you gave too many details in your question, so I apologize if I misunderstood it.

Taking into account you use mysql which doesn't have materialized views that could be used to enforce desired constraint, I see two options.
First, you can mimic materialized view with trigger[s] and a new table with unique constraint (it will work, but it's quite hard to implement in general - you have to make sure all INSERT/UPDATE/DELETE on 3 tables are handled properly by corresponding triggers).
Another way is to denormalize your schema by adding groupId to Ledger , and "extra" unique constraint on Users (userId, groupId) and Accounts (acid,groupId), and changing FKs in Ledger so it refers not to userId in Users, but to userId,groupId and not to acid, but to acid,groupId.

I hope that helps.