Cockroach: sql: support ON DELETE CASCADE constraint behavior

[dt]

Initial support for Foreign Keys only implemented the default RESTRICT behavior, that prevents deletions that would create orphaned values.

Many users (and some ORMs) utilize the CASCADE behavior, and while easy workaround exist in the case of by-ID deletions on the referenced column, other use cases require reading and additional roundtrips to the client to get similar behavior.

[dt]

The mentioned workaround for the common case of deleting by ID from a parent table where foreign keys reference that ID, is to just explicitly delete from the child tables too. in the same transaction

For example, given the schema:

CREATE TABLE customers (id SERIAL, ...); 
CREATE TABLE orders (id SERIAL, customer REFERENCES customers (id), ...); 

To get similar behavior to ON DELETE CASCADE for a query like DELETE FROM customers WHERE id = 5, instead run:

DELETE FROM orders WHERE customer = 5
DELETE FROM customers WHERE id = 5
COMMIT

For other situations, where you don't actually know what child records you might need delete, you'd need to use a SELECT first in the same transaction to find matching rows and then delete -- this adds additional roundtrips to the client and prevents some optimization like server-side retries, so it isn't ideal.

[petermattis]

@dt This is marked for 1.1 but not otherwise on the roadmap. Is the work involved on your radar? And what is the scope? Should this be moved to a later release?

[dt]

huh, nope, wasn't on my radar, haven't really scoped it out much. If we want it in 1.1, I can take a look and see if that's feasible.

[petermattis]

I don't think there is a need to squeeze this into 1.1, but taking some time now to sketch out the scope of work will be helpful for future scheduling.

[dianasaur323]

@nstewart I popped this up into 1.2 since another user asked about this. It was previously in "Later," which often becomes a hiding spot for important issues. I think this is a relatively important parity feature to have at some point.

[jjzazuet]

I'll add one vote here. Spring session needs support for this statement in order to work out of the box. I'm currently working around it as well. Thanks!

[bernielomax]

Yes please. I want one of these! :)

[nickjacques]

Adding my +1 as well. PowerDNS uses ON DELETE CASCADE in its schema to delete all of the RRs in a zone when you delete the zone.

[dianasaur323]

Thanks for all of your feedback. Stay tuned for updates on our end - we are planning our next release and currently have this tentatively slotted in with the 1.2 milestone.

[roffe]

ORY Hydra uses it as well and I was hoping Cochroach would solve my storage problems with it.

Just have to sit tight a little longer until this awesome update lands :)

[cuongdo]

This should be implemented at the same time as #17803. Also, @dt suggests designing #9897 while designing ON DELETE CASCADE to avoid future difficulties implementing the former. Also, @bdarnell has emphasized the need for specific testing to avoid performance regressions.

All of this points to the need for an RFC.

@BramGruneir, could you discuss this with @dt this week, and get the RFC started?

[vivekmenezes]

RFC: https://github.com/cockroachdb/cockroach/pull/19570

[vivekmenezes]

ON DELETE CASCADE has landed! https://github.com/cockroachdb/cockroach/pull/20064

[BramGruneir]

ON UPDATE CASCADE has landed! #21329

[BramGruneir]

ON DELETE SET NULL #21716
ON UPDATE SET NULL #21767
ON DELETE SET DEFAULT #22220
ON UPDATE SET DEFAULT #22220

Have all landed! Closing this issue as it is complete this functionality will be released as part of our 2.0 release.