How to describe multi-column foreign key in Doctrine mapping

Question

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

Answer 1

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.