Orm: DDC-1628: onUpdate parameter on @JoinColumn not supported

[doctrinebot]

Jira issue originally created by user chrisrichard:

It looks like this is in the older documentation (2.0) but not mentioned in the latest.

I need to use ON UPDATE CASCADE in a few cases. Seems odd to support onDelete and not onUpdate. Am I missing something?

[doctrinebot]

Comment created by @beberlei:

We removed onUpdate because we couldnt come up with a use-case. CAn you share yours?

[doctrinebot]

Comment created by chrisrichard:

I assume it's just the mysql driver but all the constraints get created such that you cannot change the primary keys (even if it's not an auto-gen PK). ON UPDATE CASCADE would probably be a better default.

[doctrinebot]

Comment created by darklow:

I really hope onUpdate annotation attribute will be restored.
I used it in PostgreSQL very often. In some entities in some projects Primary Key ID can be very important its sequence, and if you want to change it, then "ON UPDATE CASCADE" changed it for all the references too. It was must have feature. Now few of my applications are broken with latest doctrine orm.
Please consider restoring it back.
Thank you

[doctrinebot]

Comment created by drevolution:

+1 - onUpdate attribute is very useful for PostgreSQL databases, it controls operations with foreign keys.

[doctrinebot]

Comment created by kenkataiwa:

What do you mean, you couldn't come up with a use-case? If one is migrating data from one table to another and requires that all foreign key references be changed to map the ones in the new table created, before deleting the last table. And there are may more case. Maybe I am missing some thing here, if it's a use-case for the MySQL and PostgreSQL guys, why would you not support it.

[doctrinebot]

Comment created by @ocramius:

[~kenkataiwa] updating identifiers is something you don't do, and I sincerely also don't have a use case for this, not in the context of an ORM at least. Also, handling changes in your identifiers in the UnitOfWork is a real problem that adds a lot overhead.

[doctrinebot]

Comment created by drevolution:

+1 - onUpdate attribute is very useful for PostgreSQL databases, it controls operations with foreign keys.

I'm taking my words back. It is not a good idea to define onUpdate on foreign keys. Yes, PostgreSQL supports it but it doesn't mean that we should use it in ORM.
Thanks for your time.

[doctrinebot]

Comment created by @ocramius:

This issue is invalid, since data migration is not a task for the ORM anyway.

[doctrinebot]

Comment created by garygolden:

I have a third party table which holds users:

CREATE TABLE user
(
user*id INT UNSIGNED NOT NULL AUTO*INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
) ENGINE=InnoDB CHARSET="utf8";

In my table I want to use natural foreign key, so I reference email.

CREATE TABLE transaction (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_email VARCHAR(255) NOT NULL,
FOREIGN KEY (user_email) REFERENCES user(email)
);

I would like to RDBMS handle email updates on the foreign records.

That is a real-life use case of the onDelete, which you decided to remove.
Please, get it back if possible.

[doctrinebot]

Comment created by ibo_s:

I have a good use case and I am really missing the onUpdate cascade.
However it is working inside a many-to-one association but not in a one-to-one association.
The use case is a little complicated but I can send it to you if it could change something.

[doctrinebot]

Comment created by mrthehud:

Sorry to drag this up again, but…

I've also come across a use case as I'm migrating data in MySQL. I had to migrate data from a single table to MTI, which meant I had to investigate INFORMATION_SCHEMA, find the relevant constraints and change them in the migration script before I could migrate my data.

I appreciate that migrations and emails/usernames as PKs, are probably the only use cases, and that in general you wouldn't want the onUpdate to cascade... but is there some sort of extension that can update the constraints of all tables referencing the PK of a specified table to allow the onUpdate? If not, is that possible? Perhaps this should be raised against the doctrine migrations project... I'm not sure and would value some steer.

For anyone wondering, here's how I found the relevant constraints to batch update the onUpdate behaviour during migrations in mysql:

SELECT a.CONSTRAINT*SCHEMA, a.CONSTRAINT_NAME, a.TABLE_NAME, a.COLUMN_NAME, a.REFERENCED_TABLE_NAME, a.REFERENCED_COLUMN_NAME, c.UPDATE_RULE, c.DELETE_RULE FROM KEY_COLUMN_USAGE a LEFT JOIN REFERENTIAL_CONSTRAINTS c ON a.CONSTRAINT_NAME = c.CONSTRAINT_NAME AND a.CONSTRAINT_SCHEMA=c.CONSTRAINT_SCHEMA WHERE a.CONSTRAINT_SCHEMA = '<db-name>' AND a.REFERENCED_TABLE*NAME='<table-name>'

Then repeat the following for each constraint whose UPDATE_RULE is RESTRICT:

ALTER TABLE <table-name> DROP FOREIGN KEY `<constraint-name>`
ALTER TABLE <table-name> ADD CONSTRAINT `<constraint-name>` FOREIGN KEY (`<column-name>`) REFERENCES `<referenced-table-name>` (`<referenced-column-name>`) ON UPDATE CASCADE ON DELETE CASCADE");

[doctrinebot]

Comment created by czernitko:

Hi, we are using Doctrine Migrations as an extension in our Symfony project.
I guess everyone agrees that "on update cascade" is one of the essential parts of RDBMS. I agree that it is not essential part of ORM system. Except for the case when the ORM annotations are also used to generate Doctrine Migrations - without onUpdate keyword we are unable to automatically generate "on update cascade" constraints.
It this a sufficient use case?
With kind regards,
Peter

[doctrinebot]

Comment created by gnat:

So here's our use case.

We've built a system where we have two related tables. For simplicity, EntityA and EntityB. EntityA has a Custom ID Generator, based on some required fields. So for example the EntityA could have an id of ABC-DGE-X-YY-000001. That part works fine. EntityA has a OneToOne to EntityB.

Now one of the basic design considerations is that it is possible for an EntityB to be created before EntityA. When this occurs an basically blank EntityA is created and linked to EntityB. The ID becomes ABC-XXX-X-YY-{UUID}. This occurs because there are access rights and its necessary to query all EntityBs that don't properly link to EntityA's (basically a what EntityAs are we missing) and vise versa. The data comes from multiple sources and doesn't necessarily arrive in synchronous order. Therefore when the missing EntityA arrives, the link is detected and EntityA's id is updated. Without onUpdate cascade support it doesn't work. When I set onUpdate cascade manually everything works.

So my question is two fold. Why can't it be re-added? I'm not sure what the actual issue is since onDelete gets passed through to the DB in the end. Why can't onUpdate do the same?

If there is a design problem with having this type of key, then basically we have a storage waste as I would have an auto increment PK/FK, and then another column with our keys and used for retrieval etc...

So I'd really like to understand what the issue is having this supported.

[doctrinebot]

Issue was closed with resolution "Invalid"

[ancpru]

I agree that it would be very nice to have onUpdate back.

The primary purpose is, to keep the ability intact to create the schema from orm annotations.

In quite a few cases it may be necessary to change record keys in the db directly and it's a PITA to mess around with constraint errors.

Cascade on update is a major feature of RDBMs so I see no reason _not_ to support it in the annotiations.

[Ocramius]

The ORM doesn't support onUpdate because it is one of its operational constraints: you cannot change an identity of an entity once it's assigned.

If you need this sort of functionality, then use the DBAL schema manager directly.

[daniel-adlantic]

Minor issue: XML Mapping documentation still contains on-update in 19.1.1 Example

[havvg]

@Ocramius In conjunction with Doctrine migrations, the migrations diff command will always remove changes to the mapping information. Therefore you _cannot_ use the schema manager to add the constraint clauses, if it isn't capable of mapping onUpdate (for whatever reasons you would need it) as the diff will always remove the ON UPDATE from the foreign keys again, when generating a new migration by using the diff command.

[Ocramius]

@havvg yes, but the schema tool is also not authoritative.

[havvg]

So, what's the deal adding onUpdate just to get some use-cases straight from a DX point of view?

[Ocramius]

@havvg the ORM doesn't support schemas with SET NULL on update. In general, it doesn't support changing PKs at all.

Also, this is not DX: we're just preventing you from shooting yourself in the foot.

[paoloBirdOffice]

I have a use case when it should be super use full, when we migrate a database from id to uuid.

[Ocramius]

I have a use case when it should be super use full, when we migrate a database from id to uuid.

That shouldn't be done through the ORM, but through a DB migration.

[kshishkin]

+1 for restoring onUpdate. Simply, it saves development time and makes our lives easier at low cost. This should be reason good enough. Simplest prod use case - email as primary key. No best practice, but you could find such things.

I understand ORM looks at modelling entities rather than trying to replicate all DB mechanisms. ORM still serves to persist into database and very often for defining the database structure.

No sane person should assume their app is a single database client. Other clients may need onUpdate in their process. You don't always have control over this. Yet you may still want to use Doctrine as your structure definition repository and finding lack of onUpdate is unpleasent surprise.

My case:

1. I have data already and I want to change primary keys from integers to UUIDs.
2. I change the field definition in entity class & apply migration
3. I get char fields in the DB, but they still hold integer values
4. I need to update those keys across the whole db, but constraints won't let me
5. I can add onUpdate directly on the DB & update the keys from there, but:
6. it is much more time consuming
7. as soon as I rerun migration, the onUpdate is lost again

[Ocramius]

This is definitely not going to be re-considered: the ORM cannot operate with identifier updates (at all), and therefore won't include utilities to facilitate that.

As for the use-case you mentioned, write a migration that:

1. Alters the foreign kry to add a cascade
2. Updates the data
3. Restores the foreign key

[NotionCommotion]

I too would like to see on-update cascade supported. If it will not be, then please remove it's use from the documentation.

<one-to-one field="address" target-entity="Address" inversed-by="user">
    <cascade><cascade-remove /></cascade>
    <join-column name="address_id" referenced-column-name="id" on-delete="CASCADE" on-update="CASCADE"/>
</one-to-one>

[Ocramius]

Please send a patch for the documentation: onUpdate was removed many versions ago and is not coming back, as the ORM cannot (in any way) operate without the assumption of immutable identifiers.

[kshishkin]

I'll jump back to support Ocramius on this. In a month from now you'll be glad there are no cascade updates.

To anyone having this concerns in the future - it is frustrating at the moment when you have to deal with lack of cascade on update. This frustration is a price well worth to pay when you realize consequences of the model instability.

[NikitaKharkov]

Why we just can't add this flag like an option and support it only outside the ORM? Like a comment on a column. Why not? This also let us not to delete ON UPDATE actions from migrations.

[Ocramius]

I'm locking this conversation: this is already explained two comments above.