如何在Doctrine映射中描述多列外键 [英] How to describe multi-column foreign key in Doctrine mapping

问题描述

我们有一个数据库模式，简化（略有设计）的形式如下所示：

从用户到域的外键设置在列（domainId，groupId）上，以保证引用完整性。这个结构对于预期的目的是正常的。

但是，对于一个新的应用程序与同一个数据库通话，我现在需要创建一个映射对于两个列之间的外键关系的上述结构的原则。

以下内容：

 < entity name =Usertable =users> 
<！ - 其他字段 - > 
< many-to-one field =domaintarget-entity =Domainfetch =LAZY> 
< join-columns> 
< join-column name =domainIdreferenced-column-name =domainId/> 
< join-column name =groupIdreferenced-column-name =groupId/> 
< / join-columns> 
< / many-to-one> 
< / entity> 
  

但是这给我一个错误：
UnitOfWork.php line 2649：未定义的索引：groupId

所以，我的问题是：

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

---

/ p>

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

 code> 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`）参考`domains`（`groupId`，`domainId`） ，
 ADD CONSTRAINT`fk_users_groups` FOREIGN KEY（`groupId`）参考`groups`（`groupId`）; 
 
 ALTER TABLE`domains` 
 ADD CONSTRAINT`fk_domains_groups` FOREIGN KEY（`groupId`）参考`groups`（`groupId`）; 
  

解决方案

这不是你的问题的梦幻般的答案。此外，我从未使用过教义或教义2。但是我花了一些时间在四周，几乎都是这三个参考文献：

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

Doctrine2地图实体与复合外键... ...一个问题，没有收集很多价值，但可以折叠成你的问题作为一个重复的候选人至少。

XML Mapping Doctrine2 XML映射文档。在 multi 搜索文本时，没有价值，但在复合上搜索说：

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

这导致我进入这个维基百科<代理人的定义说明：

代理在系统内部生成，对用户或应用程序不可见
。

And

返回到您的模型，以独立性递减的顺序列出：

  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`）参考`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`）参考`domains`（`groupId`，`domainId`），
 CONSTRAINT`fk_users_groups` FOREIGN KEY `）REFERENCES`groups`（`groupId`）
）ENGINE = InnoDB DEFAULT CHARSET = utf8; 
  

有些划痕工作：

 截断表组; - 禁止从组中删除
; 
 alter table groups auto_increment 1; - 重置后，运行delete。 
 insert groups（someMoreData）values（'group0001'），（'group0002'）; 
从组中选择* 
 insert domains（groupId，someOtherData）value 
（1，'sod'），（1，'sod'），（1，'sod'），
（2，'sod' ），（2， '草皮'）; 
从域中选择* -  AI 1到5以上
插入用户（groupId，domainId，someData）值（1,1，'sd'）; - 成功
插入用户（groupId，domainId，someData）值（1,3，'sd'）; - 成功
插入用户（groupId，domainId，someData）值（1,4，'sd'）; - 错误1452 fk failure 
  

很明显，用户并不真正需要复合FK到域。相反，它只需要单列FK代入域的代理AI PK。这是足够和足够紧密的，以完成与您正在做的相同的效果。

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

无论如何，希望这是有帮助的。

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

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.

However, for a new application talking to the same database, I now need to create a mapping for Doctrine that maps the above structure, including the Foreign Key relation on two columns.

I've tried the following:

<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>

But this give me an error: UnitOfWork.php line 2649: Undefined index: groupId

So, my question is:

What is the correct method to describe a multi-column many-to-one foreign key relation in Doctrine?

---

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`);

解决方案

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 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 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 Mapping The Doctrine2 XML mapping documentation. It has no value with searching on the text multi but searching on composite says this:

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;

Some scratch work:

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

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.

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.

这篇关于如何在Doctrine映射中描述多列外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！