[D8] Really add foreign keys to core

[klonos]

This came up on Gitter...

@tabroughton

hi all, I was just taking a quick look at the DB in an ER diagram (using dbeaver) and I notice there is no referential integrity, i.e. there are no foreign keys defined they are simply indexed as MUL. It's my understanding that this causes a couple of issues, one is performance when running join queries and the other is no protection on data being deleted in one table that another depends on resulting in either orphaning entries (messy housekeeping) or breaking the db/app. There may be good reasons why the foreign keys aren't declared though and I was wondering if this is by design for some reason?

@oadaeh

@tabroughton isn't that the same with Drupal 7?

@herbdool:

@tabroughton @oadaeh exactly. The db structure approach is same as d7.

...

@herbdool:

Even if d8 releases ability to create foreign keys much of core won't use it because it'll break current way it's using SQL. https://www.drupal.org/project/drupal/issues/2254131#comment-8731193
So not much point in having it in Backdrop

@klonos:

...also, https://api.backdropcms.org/api/backdrop/core%21modules%21system%21system.api.php/function/hook_schema/1

Note that foreign key definitions are for documentation purposes only; foreign keys are not created in the database, nor are they enforced by Backdrop.

..so we seem to be documenting something that we never plan to implement. Why not remove that entirely then?

[klonos]

Related literature around the web, and d.org issues