如何在 Doctrine 映射中描述多列外键

2022-01-16 00:00:00 foreign-key-relationship php mysql doctrine doctrine-mapping

我们有一个数据库模式，以简化(略微做作)的形式，如下所示:

We have a database schema that, in simplified (slightly contrived) form, looks like:

在列(domainId、groupId)上设置从用户到域的外键，以保证参照完整性.这种结构可以很好地达到预期目的.

Where the foreign key from users to domains is set on columns (domainId, groupId), to guarantee referential integrity. This structure works fine for the intended purpose.


但是，对于与同一数据库通信的新应用程序，我现在需要为 Doctrine 创建一个映射以映射上述结构，包括 两个 列上的外键关系.

我尝试了以下方法:

<entity name="User" table="users">
   <!-- other fields -->
   <many-to-one field="domain" target-entity="Domain" fetch="LAZY">
      <join-columns>
         <join-column name="domainId" referenced-column-name="domainId"/>
         <join-column name="groupId" referenced-column-name="groupId"/>
      </join-columns>
   </many-to-one>
</entity>

但这给了我一个错误:UnitOfWork.php 第 2649 行:未定义索引:groupId

所以，我的问题是:

Doctrine 中描述多列多对一外键关系的正确方法是什么?


<小时>

为了完整起见，数据库为上面 ERD 中描述的模式创建代码:

For completeness sake, the database create code for schema as described in the ERD above:

CREATE TABLE `users` (
  `userId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `groupId` INT(10) UNSIGNED NOT NULL,
  `domainId` INT(10) UNSIGNED NOT NULL,
  `someData` VARCHAR(32),
  PRIMARY KEY (`userId`),
  KEY `key_users_groupId_domainId` (`groupId`, `domainId`)
) ENGINE=InnoDB;

CREATE TABLE `domains` (
  `domainId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `groupId` INT(10) UNSIGNED NOT NULL,
  `someOtherData` VARCHAR(32),
  PRIMARY KEY (`domainId`),
  KEY `key_domains_groupId` (`groupId`)
) ENGINE=InnoDB;


CREATE TABLE `groups` (
  `groupId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `someMoreData` VARCHAR(32),
  PRIMARY KEY (`groupId`)
) ENGINE=InnoDB;


ALTER TABLE `users`
    ADD CONSTRAINT `fk_users_domains` FOREIGN KEY (`groupId`, `domainId`) REFERENCES `domains` (`groupId`, `domainId`),
    ADD CONSTRAINT `fk_users_groups` FOREIGN KEY (`groupId`) REFERENCES `groups` (`groupId`);

ALTER TABLE `domains`
    ADD CONSTRAINT `fk_domains_groups` FOREIGN KEY (`groupId`) REFERENCES `groups` (`groupId`);

推荐答案

这不是您问题的绝佳答案.另外，我从未使用过 Doctrine 或 Doctrine2.但是我花了一些时间环顾四周，最终得到了前三个参考:

This is not a fantastic answer to your question. Also, I have never used Doctrine or Doctrine2. But the I spent some time looking around, and pretty much ended up with these first three references:

Doctrine 多重复合外键，一个问题，虽然它不显示 XML 映射，并且至少可能是脱基的它似乎与 FK 中的多列有关.并回答根据答案忽略的 Doctrine2 的某些方面.

Doctrine multiple composite foreign key, a Question though it does not show XML mappings, and may be off-base, at least it appears to be about multi-columns in an FK. And and answer about some aspects of Doctrine2 that are ignored according to an Answer.

Doctrine2 使用复合外键映射实体 ... 一个没有太多价值但可以折叠到您的问题中的问题至少作为一个受骗候选人的问题.

Doctrine2 Map entities with composite foreign keys in ... A Question that did not gather much value but could be folded into your question as a dupe candidate at least.

XML 映射 Doctrine2 XML 映射文档.在文本 multi 上搜索没有任何价值，但在 composite 上搜索这样说:

XML Mapping The Doctrine2 XML mapping documentation. It has no value with searching on the text multi but searching on composite says this:

对于复合键，您可以指定多个 id 元素，但是建议将代理键与 Doctrine 2 一起使用.

For composite keys you can specify more than one id-element, however surrogate keys are recommended for use with Doctrine 2.

这让我想到了这个维基百科定义的代理:

Which lead me to this Wikipedia definition of Surrogate that states:

代理是系统内部生成的，是不可见的给用户或应用程序.

The surrogate is internally generated by the system and is invisible to the user or application.

和 自然与替代.关于在两者之间进行选择的讨论.

And Natural vs Surrogate. A discussion of choosing between the two.

回到你的模型，按独立性降序排列:

Back to your model, listed in decreasing order of independence:

CREATE TABLE `groups` (
  `groupId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `someMoreData` VARCHAR(32),
  PRIMARY KEY (`groupId`)
) ENGINE=InnoDB;

CREATE TABLE `domains` (
  `domainId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `groupId` int(10) unsigned NOT NULL,
  `someOtherData` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`domainId`),
  KEY `key_domains_groupId` (`groupId`),
  CONSTRAINT `fk_domains_groups` FOREIGN KEY (`groupId`) REFERENCES `groups` (`groupId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `users` (
  `userId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `groupId` int(10) unsigned NOT NULL,
  `domainId` int(10) unsigned NOT NULL,
  `someData` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`userId`),
  KEY `key_users_groupId_domainId` (`groupId`,`domainId`),
  CONSTRAINT `fk_users_domains` FOREIGN KEY (`groupId`, `domainId`) REFERENCES `domains` (`groupId`, `domainId`),
  CONSTRAINT `fk_users_groups` FOREIGN KEY (`groupId`) REFERENCES `groups` (`groupId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

一些临时工作:

truncate table groups; -- disallowed
delete from groups;
alter table groups auto_increment 1; -- reset, after running delete from.
insert groups(someMoreData) values ('group0001'),('group0002');
select * from groups;
insert domains(groupId,someOtherData) values 
(1,'sod'),(1,'sod'),(1,'sod'),
(2,'sod'),(2,'sod');
select * from domains; -- AI 1 to 5 above
insert users(groupId,domainId,someData) values (1,1,'sd'); -- success
insert users(groupId,domainId,someData) values (1,3,'sd'); -- success
insert users(groupId,domainId,someData) values (1,4,'sd'); -- Error 1452 fk failure

很明显，users 并不真正需要复合 FK 到 domains.相反，它只需要在 domains 的代理 AI PK 中添加一列 FK.这足以达到与您所做的相同的效果.

It becomes quite apparent that users does not truly need a composite FK into domains. Rather, it merely needs a single column FK into the surrogate AI PK of domains. That is sufficient and tight enough to accomplish the same effect as what you are doing.

与此保持一致，users.domainId 就足够了，users.groupId 引入了非规范化，后者应该被丢弃.

In keeping with this, users.domainId is sufficient, and users.groupId introduces denormalization and the latter should be discarded.

无论如何，希望这对您有所帮助.

Anyway, hopefully this is of help.

相关文章