如何在Mysql中不相关的表执行外键约束？ [英] How to enforce foreign key constraint from unrelated tables in Mysql?

问题描述

这是我的DB结构。

是我用来创建表的脚本

  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`） ，
外键（groupid）
引用UserGroup（groupid）
）; 
 
 CREATE TABLE帐户（
 acid MEDIUMINT NOT NULL AUTO_INCREMENT，
 groupid MEDIUMINT，
 acname VARCHAR（255），
 PRIMARY KEY（`acid`） ，
外键（groupid）
引用UserGroup（groupid）
）; 
 
创建表分类帐（
 ledgerid MEDIUMINT NOT NULL AUTO_INCREMENT，
 ledgername VARCHAR（255），
 acid mediumint，
 Usrid mediumint，
 PRIMARY KEY（ledgerid）
外键（酸）
引用帐号（酸），
外键（Usrid）
引用User_det（Usrid）
）; 
  

我输入以下数据

UserGroup

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

User_det

  -------- 
  -  Usrid usrname groupid 
  - ---------------------- 
  -  1 User1 1 
  -  2 User2 2 
  / pre> 
 
 
帐户
  -------- 
  - 酸groupid acname 
 --------------------- 
  -  1 1 ac1 
  -  2 2 ac2 
  
 Ledger 
  -------- 
 -ledgerid分类帐酸酸Usrid 
 -------------------------- ------ 
  -  1分类帐1 1 
  -  2分类帐2 2 
  -  3分类帐3 1 2 
  -  4分类帐2 2 1 
 
 SELECT t1.ledgerid，t1.ledgername，t2.acname，t3.usrname 
 FROM Ledger AS t1 
 INNER JOIN帐户AS t2 ON t1.acid = t2.acid 
内部连接User_det AS t3 ON t1.Usrid = t3.Usrid; 
  
当前的表格结构允许插入违反数据库完整性的数据。
 
 
条目ledgerid 3无效
 -------------------------- --------因为acname ac1属于User2不属于的group1。因为acname ac2属于group2，因此条目帐户4是
 
 
 
无效
 
 
 User1不属于。
 
 
 
如何阻止插入这些数据？
 
 
 
现在在应用程序中，我通过PHP在BL层进行此检查。 
 
 
 
我可以在数据库级别执行此操作，因为我不需要使用PHP前端就可以从后台进行一些导入。
解决方案
使用识别关系，与此类似：
 
 
 
  
 
 
 
请注意UserGroup PK如何从钻石，双方并入底部。由于底部的一行只包含一个标识顶部的字段，因此它不能与顶部的多个行相关。
 
 
 
如果需要，您仍然可以保留其他键他们用于其他目的和/或使键上面替代（即UNIQUE约束）... 
 
 
 
 
 
 
 BTW，使用命名更一致 - 我建议总是使用单个和前缀的PK字段与未经缩名的表名... 
 
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.
解决方案
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...

                        
这篇关于如何在Mysql中不相关的表执行外键约束？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！