MySQL多层约束

Question

我可以帮助您翻译以下内容：

我在MySQL中执行数据模型时遇到了一些问题，想知道是否有更好的方法来解决这个问题。

模型：

Platforms <- Has many groups

Groups <- Has one platform, Has many SymbolSets

SymbolSets <- Belongs to many groups

我的问题是SymbolSets只能属于拥有相同平台的组。所以如果平台A有Group A与SymbolSet A，那么平台B的Group B就不应该能够获得SymbolSet A，但是平台A中的任何组都应该能够获得它。

目前我在SymbolSets和Groups之间有一个链接表，并在代码中强制执行每个平台的唯一性，但我更希望在数据库中执行解决方案，这样就没有人可以混淆它并破坏事情。我宁愿不要在第一时间就有这个多对多的关系，但这并不符合我们的业务模型。

- Noah

Answer 1

Platform     
----------
PlatformId
PRIMARY KEY (PlatformId)

Grouping        
----------
GroupingId
PlatformId
PRIMARY KEY (GroupingId, PlatformId)
FOREIGN KEY (PlatformId)            
  REFERENCES Platfrom(PlatformId)

SymbolSet         
----------
SymbolSetId 
PRIMARY KEY (SymbolSetId)

当将一个SymbolSet分配给一个Group（在SymbolSetToGroup中创建新的一行）时，首先需要将其分配给与该Group所属的Platform相同的Platform（因此首先在SymbolSetAssigned中创建新的一行）。

SymbolSetAssigned               --- assigned to Platform
-----------------
SymbolSetId 
PlatformId
PRIMARY KEY (SymbolSetId)
UNIQUE KEY (SymbolSetId, PlatformId)
FOREIGN KEY (SymbolSetId)                              
  REFERENCES SymbolSet(SymbolSetId)
FOREIGN KEY (PlatformId)            
  REFERENCES Platfrom(PlatformId)

SymbolSetToGroup
----------------
SymbolSetId 
GroupingId
PlatformId
PRIMARY KEY (SymbolSetId, GroupingId)
FOREIGN KEY (SymbolSetId, PlatformId)                              
  REFERENCES SymbolSetAssigned(SymbolSetId, PlatformId)
FOREIGN KEY (GroupingId, PlatformId)             
  REFERENCES Grouping(GroupingId, PlatformId)

- ypercubeᵀᴹ

---

在外键引用的目标中，+1 表示必须是整个 PRIMARY KEY 或整个 UNIQUE KEY。因此您需要在 SymbolSetAssigned 和 Grouping 表中以不同的方式定义键。 - Bill Karwin

实际上，在InnoDB中并非如此；它是非标准的，但外键可以引用任何非唯一索引列集。但请相信我，如果你珍视自己的理智，请不要去那里。 - Bill Karwin

@BillKarwin：是的，谢谢。这是MySQL或其他产品的限制吗？我猜向超集PK（唯一键）的FK引用将是有效的。 - ypercubeᵀᴹ

SQL-99: "外键中的列必须与引用唯一键中的相应列匹配，并具有可比较的<数据类型>，数量也必须相同。" http://kb.askmonty.org/en/constraint_type-foreign-key-constraint - Bill Karwin

MySQL 5.6：“...系统不强制要求所引用的列是唯一的或声明为NOT NULL。对于UPDATE或DELETE CASCADE等操作，处理对非唯一键或包含NULL值的键的外键引用的定义不明确。建议使用仅引用UNIQUE和NOT NULL键的外键。”我认为我不需要进一步评论那个。 - Mike Sherrill 'Cat Recall'

我觉得这个问题不是关于 NULL 值的。而是关于是否允许引用 (a,b) 这样的情况，其中 a 是 PRIMARY 或 UNIQUE（且 b 不为空），且 (a,b) 上不存在唯一键。 - ypercubeᵀᴹ