CODEWITHSUNDEEP
phpdoctrine-orm
Matteo Tassinari
Matteo Tassinari

Reputation: 18584

Doctrine 2 composite primary key with auto increment

In our database we have tables which have no single auto-increment primary key but instead a composite one, which may or may not include an auto-increment field as the first field for that primary key.

For example:

DROP TABLE IF EXISTS `gen_5_23`;
CREATE TABLE IF NOT EXISTS `gen_5_23` (
    `id_azienda` int(10) unsigned NULL DEFAULT 1,
    `id_sede` int(10) unsigned NULL DEFAULT 1,
    `revisione_documento` int(10) unsigned NULL DEFAULT 0,
    `premessa_generale` text,
    `flag_stampa` char(1) DEFAULT 'N',
    PRIMARY KEY (`id_azienda`,`id_sede`,`revisione_documento`),
    CONSTRAINT `fk_revisione_documento_gen_5_23` FOREIGN KEY (`revisione_documento`, `id_azienda`, `id_sede`) REFERENCES `agews_revisioni_documenti` (`revisione_documento`, `id_azienda`, `id_sede`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `gen_5_23_consumi`;
CREATE TABLE IF NOT EXISTS `gen_5_23_consumi` (
    `id_consumo` int(10) unsigned AUTO_INCREMENT,
    `id_azienda` int(10) unsigned NULL DEFAULT 1,
    `id_sede` int(10) unsigned NULL DEFAULT 1,
    `revisione_documento` int(10) unsigned NULL DEFAULT 0,
    `consumo` varchar(255) NULL DEFAULT NULL,
    `unita` varchar(255) NULL DEFAULT NULL,
    `valore` float(11,2) NULL DEFAULT 0,
    PRIMARY KEY (id_consumo,`id_azienda`,`id_sede`,`revisione_documento`),
    CONSTRAINT `fk_main_gen_5_23_consumi` FOREIGN KEY (`id_azienda`, `id_sede`, `revisione_documento`) REFERENCES `gen_5_23` (`id_azienda`, `id_sede`, `revisione_documento`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The key in gen_5_23_consumi is defined as is 'cause there are procedures in our webapp which would blindly take a row, change only id_azienda or id_sede or revisione_documento and then re-insert it, in this way the row would be valid, as it would not be if the primary key was only id_consumo.

We're considering to start using doctrine 2 for database management, but I do not understand from the docs how would you implement such an entity, if it is ever possible.

Upvotes: 5

Views: 6099

Answers (2)

Dennis
Dennis

Reputation: 8101

As of now you cannot do composite (primary, foreign) keys with AUTO INCREMENT option.

You will have to use your own code to select MAX Key from your table.

then set MAX + 1 on your Entity's id.

Upvotes: 0

WizardZ
WizardZ

Reputation: 1397

It is possible but not out of the box. You can have composite key as primary keys for your entities: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/composite-primary-keys.html but auto increment implementation should be done in your code because of

Every entity with a composite key cannot use an id generator other than “ASSIGNED”. That means the ID fields have to have their values set before you call EntityManager#persist($entity).

To emulate auto increment behavior you can have sequence table with id as the auto increment PK and create an entity for this table. Add relation from your main entity to entity that represents auto increment sequence, see class ArticleAttribute from the page above, your code should be very similar:

Class Gen523consumiAuto
{
    /** @Id @Column(type="integer") @GeneratedValue */
    private $id;
}

Class Gen523consumi
{
    /** @Id @OneToOne(targetEntity="Gen523consumiAuto") */
    private $idConsumo;

    /** @Id @Column(type="integer") */
    private $idAzienda;

    /** @Id @Column(type="integer") */
    private $idSede;

    /** @Id @Column(type="integer") */
    private $revisioneDocumento;
}

Upvotes: 3

Related Questions