Missing foreign keys

[stem]

I've recently found that at my very big surprise, there were a lot of foreign keys missing in my schema.
For exemple, on a brand new store, inventory units don't have any FK, even to line items.

There was a try to fix that through #2036 and it was briefly discussed in #2253 but it seems that no solution has been found.

I'm posting here as the issue already exists and as I think this topics needs a discussion more than an issue at the moment.

One way I think about "fixing" this, is by having a migration which is run only on setups (TBH, I don't know if it's even possible but it would set new store on a great start).

Another way would be to try to create each foreign key in a transaction and fallback to NOOP if corrupt data exists.

Yet another way could be to provide a migration with "invalid" FK to Postgres users as this DB engine supports them with the NOT VALID option. We could create a constraint that will not check old values but ensure new inserts or updates are correct so in the long run, old shops will be in a far better shape with their data than they are now.

Is the core team willing to reopen discussion on this topic ?
Do you have other options in mind ?

[jarednorman]

I don't think discussion was ever closed on this topic. #2253 is still open because, as far as I know, we'd still like to improve the completeness of our foreign keys. Probably worth continuing the discussion over on that issue since you have thoughts on how we might be able to do it.

[kennyadsl]

Yes, we'd like to add missing FKs but this will break a lot of old stores that have invalid data in production.

One way I think about "fixing" this, is by having a migration which is run only on setups (TBH, I don't know if it's even possible but it would set new store on a great start).

I also see this as a path forward, to expand what you propsed:

1. we could find a way to initially install that migration only on new stores
2. provide a way for old stores that want to update their DBs to optionally add it (maybe we can provide a generic script to fix broken data that stores can customize based on their data, but I'm not sure if it is doable).
3. we could add a deprecation warning that this will be the new default in the next major

Another way would be to try to create each foreign key in a transaction and fallback to NOOP if corrupt data exists.

This could solve the issue but then we'd have unpredictable database structures to deal with, which IMHO is worst in terms of maintainability for Solidus.