doctrine/archived-orm
1
0
Fork 0
mirror of https://github.com/doctrine/orm.git synced 2026-03-24 06:52:09 +01:00
Code Issues 5.3k Packages Projects Releases 100 Wiki Activity

Use composite foreign key a part of a composite primary key #5474

New Issue
Closed
opened 2026-01-22 15:08:41 +01:00 by admin · 16 comments
admin commented 2026-01-22 15:08:41 +01:00
Owner
Copy Link

Originally created by @TheBay0r on GitHub (Mar 22, 2017).

Originally assigned to: @Ocramius on GitHub.

I try to create a composite primary key where one part of it, is a composite foreign key. The code looks like following:

    /**
     * @var a
     *
     * @ORM\Id
     * @ORM\ManyToOne(targetEntity="a")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="a_id", referencedColumnName="a_id", onDelete="CASCADE")
     * })
     */
    protected $a;

    /**
     * @var b
     *
     * @ORM\Id
     * @ORM\ManyToOne(targetEntity="b")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="b_id", referencedColumnName="b_id", nullable=false, onDelete="CASCADE"),
     *   @ORM\JoinColumn(name="c_id", referencedColumnName="c_id", nullable=false, onDelete="CASCADE")
     * })
     */
    protected $b;

    /**
     * @var d
     *
     * @ORM\Id
     * @ORM\ManyToOne(targetEntity="d")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="d_id", referencedColumnName="d_id", nullable=false, onDelete="CASCADE")
     * })
     */
    protected $d;

Sadly this scenario doesn't seem to work. Am I doing something wrong or is it simply not supported?

Without having a composite foreign key I at least get the composite primary.. But what I would want to have in the DB is something like:

CREATE TABLE `e` (
  `a_id` int(10) unsigned NOT NULL,
  `b_id` int(10) unsigned NOT NULL,
  `c_id` int(10) unsigned NOT NULL,
  `d_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`a_id`,`b_id`,`c_id`,`d_id`),
  CONSTRAINT `FK_D3B508621E27F6BF` FOREIGN KEY (`a_id`) REFERENCES `a` (`a_id`) ON DELETE CASCADE,
  CONSTRAINT `FK_D3B508623E2E969B1DFA7C8F` FOREIGN KEY (`b_id`, `c_id`) REFERENCES `b` (`b_id`, `c_id`) ON DELETE CASCADE,
  CONSTRAINT `FK_D3B50862AA334807` FOREIGN KEY (`d_id`) REFERENCES `d` (`d_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Originally created by @TheBay0r on GitHub (Mar 22, 2017). Originally assigned to: @Ocramius on GitHub. I try to create a composite primary key where one part of it, is a composite foreign key. The code looks like following: ```php /** * @var a * * @ORM\Id * @ORM\ManyToOne(targetEntity="a") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="a_id", referencedColumnName="a_id", onDelete="CASCADE") * }) */ protected $a; /** * @var b * * @ORM\Id * @ORM\ManyToOne(targetEntity="b") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="b_id", referencedColumnName="b_id", nullable=false, onDelete="CASCADE"), * @ORM\JoinColumn(name="c_id", referencedColumnName="c_id", nullable=false, onDelete="CASCADE") * }) */ protected $b; /** * @var d * * @ORM\Id * @ORM\ManyToOne(targetEntity="d") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="d_id", referencedColumnName="d_id", nullable=false, onDelete="CASCADE") * }) */ protected $d; ``` Sadly this scenario doesn't seem to work. Am I doing something wrong or is it simply not supported? Without having a composite foreign key I at least get the composite primary.. But what I would want to have in the DB is something like: ```mysql CREATE TABLE `e` ( `a_id` int(10) unsigned NOT NULL, `b_id` int(10) unsigned NOT NULL, `c_id` int(10) unsigned NOT NULL, `d_id` int(10) unsigned NOT NULL, PRIMARY KEY (`a_id`,`b_id`,`c_id`,`d_id`), CONSTRAINT `FK_D3B508621E27F6BF` FOREIGN KEY (`a_id`) REFERENCES `a` (`a_id`) ON DELETE CASCADE, CONSTRAINT `FK_D3B508623E2E969B1DFA7C8F` FOREIGN KEY (`b_id`, `c_id`) REFERENCES `b` (`b_id`, `c_id`) ON DELETE CASCADE, CONSTRAINT `FK_D3B50862AA334807` FOREIGN KEY (`d_id`) REFERENCES `d` (`d_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; ```
admin added the BugCan't FixInvalid labels 2026-01-22 15:08:41 +01:00
admin closed this issue 2026-01-22 15:08:41 +01:00
admin commented 2026-01-22 15:08:41 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Mar 22, 2017):

Sadly this scenario doesn't seem to work.

What does it do?

@Ocramius commented on GitHub (Mar 22, 2017): > Sadly this scenario doesn't seem to work. What does it do?
admin commented 2026-01-22 15:08:42 +01:00
Author
Owner
Copy Link

@TheBay0r commented on GitHub (Mar 22, 2017):

Oh sry, forgot to add that:

CREATE TABLE `e` (
  `a_id` int(10) unsigned NOT NULL,
  `b_id` int(10) unsigned NOT NULL,
  `c_id` int(10) unsigned NOT NULL,
  `d_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`a_id`,`d_id`),
  KEY `IDX_D3B508623E2E969B1DFA7C8F` (`b_id`,`c_id`),
 CONSTRAINT `FK_D3B508621E27F6BF` FOREIGN KEY (`a_id`) REFERENCES `a` (`a_id`) ON DELETE CASCADE,
  CONSTRAINT `FK_D3B508623E2E969B1DFA7C8F` FOREIGN KEY (`b_id`, `c_id`) REFERENCES `b` (`b_id`, `c_id`) ON DELETE CASCADE,
  CONSTRAINT `FK_D3B50862AA334807` FOREIGN KEY (`d_id`) REFERENCES `d` (`d_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
@TheBay0r commented on GitHub (Mar 22, 2017): Oh sry, forgot to add that: ```mysql CREATE TABLE `e` ( `a_id` int(10) unsigned NOT NULL, `b_id` int(10) unsigned NOT NULL, `c_id` int(10) unsigned NOT NULL, `d_id` int(10) unsigned NOT NULL, PRIMARY KEY (`a_id`,`d_id`), KEY `IDX_D3B508623E2E969B1DFA7C8F` (`b_id`,`c_id`), CONSTRAINT `FK_D3B508621E27F6BF` FOREIGN KEY (`a_id`) REFERENCES `a` (`a_id`) ON DELETE CASCADE, CONSTRAINT `FK_D3B508623E2E969B1DFA7C8F` FOREIGN KEY (`b_id`, `c_id`) REFERENCES `b` (`b_id`, `c_id`) ON DELETE CASCADE, CONSTRAINT `FK_D3B50862AA334807` FOREIGN KEY (`d_id`) REFERENCES `d` (`d_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; ```
admin commented 2026-01-22 15:08:42 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Mar 22, 2017):

That seems really buggy - shouldn't just drop columns like that...

Does the ORM schema validator work?

@Ocramius commented on GitHub (Mar 22, 2017): That seems really buggy - shouldn't just drop columns like that... Does the ORM schema validator work?
admin commented 2026-01-22 15:08:43 +01:00
Author
Owner
Copy Link

@TheBay0r commented on GitHub (Mar 23, 2017):

Everything seems fine according to the validator:

php bin/console doctrine:schema:validate
[Mapping]  OK - The mapping files are correct.
[Database] OK - The database schema is in sync with the mapping files.
@TheBay0r commented on GitHub (Mar 23, 2017): Everything seems fine according to the validator: ```bash php bin/console doctrine:schema:validate [Mapping] OK - The mapping files are correct. [Database] OK - The database schema is in sync with the mapping files. ```
admin commented 2026-01-22 15:08:43 +01:00
Author
Owner
Copy Link

@TheBay0r commented on GitHub (Mar 24, 2017):

Hm, looks like there was a special character in front of @ORM\Id, so doctrine wouldn't recognise it, at

    /**
     * @var b
     *
     * @ORM\Id
     * @ORM\ManyToOne(targetEntity="b")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="b_id", referencedColumnName="b_id", nullable=false, onDelete="CASCADE"),
     *   @ORM\JoinColumn(name="c_id", referencedColumnName="c_id", nullable=false, onDelete="CASCADE")
     * })
     */
    protected $b;

Now that I have re-written that line I get:

[Doctrine\ORM\Mapping\MappingException]
It is not possible to map entity 'b' with a composite primary key as part of the 
primary key of another entity 'e#b'.

This actually answers the question i guess 😞

@TheBay0r commented on GitHub (Mar 24, 2017): Hm, looks like there was a special character in front of `@ORM\Id`, so doctrine wouldn't recognise it, at ```php /** * @var b * * @ORM\Id * @ORM\ManyToOne(targetEntity="b") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="b_id", referencedColumnName="b_id", nullable=false, onDelete="CASCADE"), * @ORM\JoinColumn(name="c_id", referencedColumnName="c_id", nullable=false, onDelete="CASCADE") * }) */ protected $b; ``` Now that I have re-written that line I get: ```bash [Doctrine\ORM\Mapping\MappingException] It is not possible to map entity 'b' with a composite primary key as part of the primary key of another entity 'e#b'. ``` This actually answers the question i guess 😞
admin commented 2026-01-22 15:08:43 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Mar 24, 2017):

@TheBay0r yeah, it's currently a limitation, and it was never cleaned up because it's a huge amount of work for very little gain. It's much simpler to have a synthetic PK and unique indexes for what your model understands as PK.

@Ocramius commented on GitHub (Mar 24, 2017): @TheBay0r yeah, it's currently a limitation, and it was never cleaned up because it's a huge amount of work for very little gain. It's much simpler to have a synthetic PK and unique indexes for what your model understands as PK.
admin commented 2026-01-22 15:08:43 +01:00
Author
Owner
Copy Link

@yurii-github commented on GitHub (Sep 24, 2017):

the limitation is real.. instead of 2 tables i'm forced to use 4 tables

@yurii-github commented on GitHub (Sep 24, 2017): the limitation is real.. instead of 2 tables i'm forced to use 4 tables
admin commented 2026-01-22 15:08:44 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Sep 24, 2017):

@yurii-github if you want, you can analyse the code locations affected by this. The main problem is generating unique and non-colliding flat identifier fields for hashing within the UnitOfWork and all layers of the ORM

@Ocramius commented on GitHub (Sep 24, 2017): @yurii-github if you want, you can analyse the code locations affected by this. The main problem is generating unique and non-colliding flat identifier fields for hashing within the UnitOfWork and all layers of the ORM
admin commented 2026-01-22 15:08:44 +01:00
Author
Owner
Copy Link

@garycla commented on GitHub (Oct 18, 2017):

... because it's a huge amount of work for very little gain.

I can understand this is a huge amount of work (I thank you for your work on this very good tool) and it is true we can turn this limitation around but when you say it is very little gain... I disagree. Having composite keys is rather common (and doctrine is said to support composite keys natively : "Doctrine 2 supports composite primary keys natively. Composite keys are a very powerful relational database concept"). What you say is just that we can have composite PK but not many to many relationships between 2 entities like this.
In my experience, this is very common and models become much more complex and less readable to handle this (much more FK or joins)

@garycla commented on GitHub (Oct 18, 2017): > ... because it's a huge amount of work for very little gain. I can understand this is a huge amount of work (I thank you for your work on this very good tool) and it is true we can turn this limitation around but when you say it is very little gain... I disagree. Having composite keys is rather common (and doctrine is said to support composite keys natively : "_Doctrine 2 supports composite primary keys natively. Composite keys are a very powerful relational database concept_"). What you say is just that we can have composite PK but not many to many relationships between 2 entities like this. In my experience, this is very common and models become much more complex and less readable to handle this (much more FK or joins)
admin commented 2026-01-22 15:08:44 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Oct 18, 2017):

not many to many relationships between 2 entities like this.

These are supported. What isn't supported is using a composite identity of an associated entity as part of the identity of an entity.

In my experience, this is very common and models become much more complex and less readable to handle this (much more FK or joins)

You are more than welcome to start implementing a solution for this problem-space, then (you can start from just a test).

Doctrine ORM is already quite ahead on anything regarding composite PKs, but it is not a trivial problem to solve within PHP's performance constraints.

@Ocramius commented on GitHub (Oct 18, 2017): > not many to many relationships between 2 entities like this. These are supported. What isn't supported is using a composite identity of an associated entity as part of the identity of an entity. > In my experience, this is very common and models become much more complex and less readable to handle this (much more FK or joins) You are more than welcome to start implementing a solution for this problem-space, then (you can start from just a test). Doctrine ORM is already quite ahead on anything regarding composite PKs, but it is not a trivial problem to solve within PHP's performance constraints.
admin commented 2026-01-22 15:08:45 +01:00
Author
Owner
Copy Link

@garycla commented on GitHub (Oct 18, 2017):

These are supported. What isn't supported is using a composite identity of an associated entity as part of the identity of an entity.

I'm not sure I have understood (i'm french). Anyway, i think what you say is what i meant to be common : when I have an entity with a FK to another one, being part of its PK, this PK can't take part in a many to many relationship, which is a situation i meet quite often

You are more than welcome to start implementing a solution for this problem-space, then (you can start from just a test).

It seems to be a high hill for me (Doctrine is quite a big project) but I can try to help...

@garycla commented on GitHub (Oct 18, 2017): > These are supported. What isn't supported is using a composite identity of an associated entity as part of the identity of an entity. I'm not sure I have understood (i'm french). Anyway, i think what you say is what i meant to be common : when I have an entity with a FK to another one, being part of its PK, this PK can't take part in a many to many relationship, which is a situation i meet quite often > You are more than welcome to start implementing a solution for this problem-space, then (you can start from just a test). It seems to be a high hill for me (Doctrine is quite a big project) but I can try to help...
admin commented 2026-01-22 15:08:45 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Oct 18, 2017):

Is this supported even when the entities are generated from the database ? My tests failed...

The entity generator is a rudimentary tool only meant to get you started.

It seems to be a high hill for me (Doctrine is quite a big project) but I can try to help...

If you look at https://github.com/doctrine/doctrine2/tree/master/tests/Doctrine/Tests/ORM/Functional/Ticket you can already find examples of existing integration tests.

@Ocramius commented on GitHub (Oct 18, 2017): > Is this supported even when the entities are generated from the database ? My tests failed... The entity generator is a rudimentary tool only meant to get you started. > It seems to be a high hill for me (Doctrine is quite a big project) but I can try to help... If you look at https://github.com/doctrine/doctrine2/tree/master/tests/Doctrine/Tests/ORM/Functional/Ticket you can already find examples of existing integration tests.
admin commented 2026-01-22 15:08:45 +01:00
Author
Owner
Copy Link

@garycla commented on GitHub (Oct 18, 2017):

sorry for the update of my comment while you were answering. I re read it and saw i didn't answered right.

@garycla commented on GitHub (Oct 18, 2017): sorry for the update of my comment while you were answering. I re read it and saw i didn't answered right.
admin commented 2026-01-22 15:08:45 +01:00
Author
Owner
Copy Link

@garycla commented on GitHub (Oct 18, 2017):

The entity generator is a rudimentary tool only meant to get you started.

As I am in an offensive day :-) I'd say this is some kind of pity as i like to create my database from modelling tools (when you have more than 100 tables, it becomes hard to keep the whole schema in mind without graphical tool). So it would be very practical to generate entities from DB...

Well. Anyway, all this is to tickle. Doctrine is a very good tool and I will be able to manage these little flaws

@garycla commented on GitHub (Oct 18, 2017): > The entity generator is a rudimentary tool only meant to get you started. As I am in an offensive day :-) I'd say this is some kind of pity as i like to create my database from modelling tools (when you have more than 100 tables, it becomes hard to keep the whole schema in mind without graphical tool). So it would be very practical to generate entities from DB... Well. Anyway, all this is to tickle. Doctrine is a very good tool and I will be able to manage these little flaws
admin commented 2026-01-22 15:08:45 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Oct 18, 2017):

So it would be very practical to generate entities from DB...

It has been abused to the point that we're probably dropping the generator from ORM core at some point. Don't expect it to become better: entities should be designed first, and the database is a detail that comes later. If you go the opposite way, there's a good chance that the ORM is more of a problem than an useful tool for you.

@Ocramius commented on GitHub (Oct 18, 2017): > So it would be very practical to generate entities from DB... It has been abused to the point that we're probably dropping the generator from ORM core at some point. Don't expect it to become better: entities should be designed first, and the database is a detail that comes later. If you go the opposite way, there's a good chance that the ORM is more of a problem than an useful tool for you.
admin commented 2026-01-22 15:08:46 +01:00
Author
Owner
Copy Link

@garycla commented on GitHub (Oct 18, 2017):

I can understand this.
But it is very "painful" to write php "stupid" classes for 100's of objects after having modelled them in a graphical tool.
In this case, maybe would it be interesting to provide a generator from a UML modeller.

@garycla commented on GitHub (Oct 18, 2017): I can understand this. But it is very "painful" to write php "stupid" classes for 100's of objects after having modelled them in a graphical tool. In this case, maybe would it be interesting to provide a generator from a UML modeller.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
BC Break
Bug
Can't Fix
Deprecation
Documentation
DQL
Duplicate
Failing Test
Hacktoberfest
Improvement
Improvement
Incomplete
Invalid
Missing Tests
New Feature
PR Created
pull-request
Mirrored from GitHub Pull Request
 Question
Regression
Status: Waiting feedback
Waiting feedback
WIP
Won't Fix
No Label Bug Can't Fix Invalid
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
admin
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5474
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?