Impossible to have part of a composite key to be null in a ternary relationship ? #5659

New Issue

admin · 2026-01-22T15:13:56+01:00

admin commented

2026-01-22 15:13:56 +01:00

Originally created by @CPASimUSante on GitHub (Aug 24, 2017).

Originally assigned to: @Ocramius on GitHub.

I have an entity that holds relation with 3 entities Session, User and Institution.

/**
 * @ORM\Table(name="institutioncontactsessionorganization")
 * @ORM\Entity(repositoryClass="MyBundle\Repository\InstitutionContactSessionOrganizationRepository")
 * @UniqueEntity(
 *     fields={"icsoInstitution", "icsoSession", "icsoUser"},
 *     errorPath="departmentName",
 *     message="this association already exists"
 * )
 */
class InstitutionContactSessionOrganization
{
  /**
   * @ORM\Id
   * @ORM\ManyToOne(targetEntity="MyBundle\Entity\Institution", inversedBy="icsos")
   * @ORM\JoinColumn(name="institutionId", referencedColumnName="id", nullable=true)
   */
  private $icsoInstitution;

  /**
   * @ORM\Id
   * @ORM\ManyToOne(targetEntity="MyBundle\Entity\Session", inversedBy="icsos")
   * @ORM\JoinColumns({
   *   @ORM\JoinColumn(name="sessionId", referencedColumnName="id", nullable=true)
   * })
   */
  private $icsoSession;

  /**
   * @ORM\Id
   * @ORM\ManyToOne(targetEntity="MyBundle\Entity\User", inversedBy="icsos")
   * @ORM\JoinColumns({
   *   @ORM\JoinColumn(name="userId", referencedColumnName="id", nullable=true)
   * })
   */
  private $icsoUser;
...
}

but when i try to generate the schema, i have :

CREATE TABLE institutioncontactsessionorganization (departmentName VARCHAR(255) DEFAULT NULL, phone VARCHAR(16) DEFAULT NULL, institutionId INT NOT NULL, sessionId INT NOT NULL, userId INT NOT NULL, INDEX IDX_43EDEF2F76065B2 (institutionId), INDEX IDX_43EDEF23950B5F6 (sessionId), INDEX IDX_43EDEF264B64DCC (userId), PRIMARY KEY(institutionId, sessionId, userId)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
ALTER TABLE institutioncontactsessionorganization ADD CONSTRAINT FK_43EDEF2F76065B2 FOREIGN KEY (institutionId) REFERENCES institution (id);
ALTER TABLE institutioncontactsessionorganization ADD CONSTRAINT FK_43EDEF23950B5F6 FOREIGN KEY (sessionId) REFERENCES session (id);
ALTER TABLE institutioncontactsessionorganization ADD CONSTRAINT FK_43EDEF264B64DCC FOREIGN KEY (userId) REFERENCES user (id);

the three part of the key have NOT NULL even if i add explicitly nullable=true...

i've validated the schema with a app/console doctrine:schema:validate and it's OK

I've read the doc for ID annotation
and
those for join column with nullable

Finally , I'm not sure to understand the implication of "General Considerations" part for the composite keys in the doc

It seems wrong to have to change a "regular" ternary relationship for a classical Entity with onr Primary key and userId, sessionId, institutionId as foreign keys, which can be null.
Why can't a keys like (userIdValue, null, institutionIdValue), (userIdValue, sessionId, institutionIdValue), (null, null, institutionIdValue)... as long as the composite key stay unique

Did i miss something in the doc, is it a limitation or is is a real bug ?
Thank you

Originally created by @CPASimUSante on GitHub (Aug 24, 2017). Originally assigned to: @Ocramius on GitHub. I have an entity that holds relation with 3 entities Session, User and Institution. ``` /** * @ORM\Table(name="institutioncontactsessionorganization") * @ORM\Entity(repositoryClass="MyBundle\Repository\InstitutionContactSessionOrganizationRepository") * @UniqueEntity( * fields={"icsoInstitution", "icsoSession", "icsoUser"}, * errorPath="departmentName", * message="this association already exists" * ) */ class InstitutionContactSessionOrganization { /** * @ORM\Id * @ORM\ManyToOne(targetEntity="MyBundle\Entity\Institution", inversedBy="icsos") * @ORM\JoinColumn(name="institutionId", referencedColumnName="id", nullable=true) */ private $icsoInstitution; /** * @ORM\Id * @ORM\ManyToOne(targetEntity="MyBundle\Entity\Session", inversedBy="icsos") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="sessionId", referencedColumnName="id", nullable=true) * }) */ private $icsoSession; /** * @ORM\Id * @ORM\ManyToOne(targetEntity="MyBundle\Entity\User", inversedBy="icsos") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="userId", referencedColumnName="id", nullable=true) * }) */ private $icsoUser; ... } ``` but when i try to generate the schema, i have : ``` CREATE TABLE institutioncontactsessionorganization (departmentName VARCHAR(255) DEFAULT NULL, phone VARCHAR(16) DEFAULT NULL, institutionId INT NOT NULL, sessionId INT NOT NULL, userId INT NOT NULL, INDEX IDX_43EDEF2F76065B2 (institutionId), INDEX IDX_43EDEF23950B5F6 (sessionId), INDEX IDX_43EDEF264B64DCC (userId), PRIMARY KEY(institutionId, sessionId, userId)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; ALTER TABLE institutioncontactsessionorganization ADD CONSTRAINT FK_43EDEF2F76065B2 FOREIGN KEY (institutionId) REFERENCES institution (id); ALTER TABLE institutioncontactsessionorganization ADD CONSTRAINT FK_43EDEF23950B5F6 FOREIGN KEY (sessionId) REFERENCES session (id); ALTER TABLE institutioncontactsessionorganization ADD CONSTRAINT FK_43EDEF264B64DCC FOREIGN KEY (userId) REFERENCES user (id); ``` the three part of the key have NOT NULL even if i add explicitly nullable=true... i've validated the schema with a app/console doctrine:schema:validate and it's OK I've read the doc [for ID annotation](http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/annotations-reference.html#annref-id) and those for j[oin column with nullable](http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/annotations-reference.html#joincolumn) Finally , I'm not sure to understand the implication of ["General Considerations" part for the composite keys in the doc](http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/composite-primary-keys.html#general-considerations) It seems wrong to have to change a "regular" ternary relationship for a classical Entity with onr Primary key and userId, sessionId, institutionId as foreign keys, which can be null. Why can't a keys like (userIdValue, null, institutionIdValue), (userIdValue, sessionId, institutionIdValue), (null, null, institutionIdValue)... as long as the composite key stay unique Did i miss something in the doc, is it a limitation or is is a real bug ? Thank you

admin added the Bug Invalid labels 2026-01-22 15:13:56 +01:00

admin closed this issue

2026-01-22 15:13:56 +01:00

admin commented

2026-01-22 15:13:57 +01:00

@Ocramius commented on GitHub (Aug 25, 2017):

@CPASimUSante Doctrine ORM does not allow nor support NULL for primary key fields. If a field is an identifier, then it should be forced to be NOT NULL by the ORM.

From the MySQL docs, for example:

PRIMARY KEY

A unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY. The name of a PRIMARY KEY is always PRIMARY, which thus cannot be used as the name for any other kind of index.

If you do not have a PRIMARY KEY and an application asks for the PRIMARY KEY in your tables, MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY.

In other engines, a primary key with nullable fields would also make no sense, because NULL is not used for uniqueness comparison (as it cannot be compared to other values), so you may end up having two records with a PK (1, 2, NULL), even though you have a primary key set on these 3 columns.

Closing as invalid.

@Ocramius commented on GitHub (Aug 25, 2017): @CPASimUSante Doctrine ORM does not allow nor support `NULL` for primary key fields. If a field is an identifier, then it should be forced to be `NOT NULL` by the ORM. From [the MySQL docs](https://dev.mysql.com/doc/refman/5.7/en/create-table.html), for example: > PRIMARY KEY > > A unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY. The name of a PRIMARY KEY is always PRIMARY, which thus cannot be used as the name for any other kind of index. > > If you do not have a PRIMARY KEY and an application asks for the PRIMARY KEY in your tables, MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY. In other engines, a primary key with nullable fields would also make no sense, because `NULL` is not used for uniqueness comparison (as it cannot be compared to other values), so you may end up having two records with a PK `(1, 2, NULL)`, even though you have a primary key set on these 3 columns. Closing as `invalid`.

admin commented

2026-01-22 15:13:57 +01:00

@CPASimUSante commented on GitHub (Aug 25, 2017):

Thank you for your confirmation/explanation. I'll use the "regular" entity system with primary key then.

@CPASimUSante commented on GitHub (Aug 25, 2017): Thank you for your confirmation/explanation. I'll use the "regular" entity system with primary key then.

admin commented

2026-01-22 15:13:57 +01:00

@CPASimUSante commented on GitHub (Aug 25, 2017):

Not to reopen, but as a side note, NULL can not indeed be compared to a value, but it can be checked : null or not null, which does 2 states => values possible. But i understand it would be a different kind of check...

@CPASimUSante commented on GitHub (Aug 25, 2017): Not to reopen, but as a side note, NULL can not indeed be compared to a value, but it can be checked : null or not null, which does 2 states => values possible. But i understand it would be a different kind of check...

admin commented

2026-01-22 15:13:58 +01:00

@Ocramius commented on GitHub (Aug 25, 2017):

The DB doesn't check it with the uniqueness of a PRIMARY KEY constraint, which is the first problem.

@Ocramius commented on GitHub (Aug 25, 2017): The DB doesn't check it with the uniqueness of a `PRIMARY KEY` constraint, which is the first problem.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5659