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

[CPASimUSante]

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

[Ocramius]

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

[CPASimUSante]

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

[CPASimUSante]

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

[Ocramius]

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