doctrine / orm

Doctrine Object Relational Mapper (ORM)
https://www.doctrine-project.org/projects/orm.html
MIT License
9.91k stars 2.5k forks source link

Force Doctrien to create GUID fields as VARCHAR(255) or to update exsting fields to CHAR(36) #6093

Open SDPrio opened 7 years ago

SDPrio commented 7 years ago

I am using Doctrine within a Symfony 2.8 project. The entities / DB tables within the project are using a GUID field as ID. In previous Doctrine Versions these field where created as VARCHAR(255), while after an Update (see below), they are now created as CHAR(36):

doctrine/dbal               v2.4.4  --> 2.5.5
doctrine/doctrine-bundle    v1.2.0  --> 1.6.2           
doctrine/orm                v2.4.8  --> 2.5.5

The code change can be found in dbal/lib/Doctrine/DBAL/Platforms/AbstractPlatform.php in the getGuidTypeDeclarationSQL method.

Of course it makes sense to store GUIDs as CHAR(36) since this is their defined length and thus a VARCHAR(255) wastes space. However this change brings up a new Problem:

I added a new entity that should use a ManyToOne relationship to an existing entity via its guid field:

// OtherEntity

/**
 * @ORM\Id
 * @ORM\Column(name="guid", type="guid", unique=true)
 */
protected $guid;

// New Entity

/**
 * @ORM\ManyToOne(targetEntity="OtherEntity")
 * @ORM\JoinColumn(name="other_guid", referencedColumnName="guid", nullable=false, onDelete="SET NULL")
 */
protected $otherEntity;

Doctrine dumps the following SQL staments to create the table for the NewEntity when using php app/console doctrine:schema:update --dump-sql:

CREATE TABLE new_entity (guid CHAR(36) NOT NULL COMMENT '(DC2Type:guid)', other_guid CHAR(36) NOT NULL COMMENT '(DC2Type:guid)', ...

ALTER TABLE new_entity ADD CONSTRAINT FK_D3D1CD16A7FC4818 FOREIGN KEY (other_guid) REFERENCES other_entity (guid) ON DELETE SET NULL;

The execution however fails with

[Doctrine\DBAL\Exception\DriverException]                                                                                                                                     
An exception occurred while executing 'ALTER TABLE new_entity ADD CONSTRAINT FK_D3D1CD16A7FC4818 FOREIGN KEY (other_guid) REFERENCES other_entity (guid) ON DELETE SET NULL':  
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint 

The problem is, that the guid field of other_entity is defined as VARCHAR(255) (created with old Doctrine version), while the other_guid field of new_entity is now created as CHAR(36). Mapping field of different types is not possible and leads to the error shown above. When I manually create the new_entity table using VARCHAR(255) instead, everything works fine.

How can I solve this?

Is it possible to force Doctrine to create guidas as VARCHAR(255)? Of course it would be better to update the existing tables to also use CHAR(36), but doctrine:schema:update does not do that.

Any idea?

Ocramius commented 7 years ago

@SDPrio what you are hitting is a schema upgrade issue. Just needs a manual migration for now, where the altering is done on both sides, and the FK is added afterwards.

If I get this correctly, doctrine is not doing that, and therefore you have the issue. Is it possible for you to try simulating this scenario with just the DBAL schema diffing tools, in a test case?