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

New Issue

admin · 2026-01-22T15:03:54+01:00

admin commented

2026-01-22 15:03:54 +01:00

Originally created by @SDPrio on GitHub (Oct 20, 2016).

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?

Originally created by @SDPrio on GitHub (Oct 20, 2016). 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?

admin commented

2026-01-22 15:03:54 +01:00

@Ocramius commented on GitHub (Oct 25, 2016):

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

@Ocramius commented on GitHub (Oct 25, 2016): @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?

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#5297