Enable extended foreign keys

Hello again ,

I thought to some database hack I learned and practiced that requires to not use current implementations of Foreign Keys which is a problem for data integrity.
Imagine that you have some model that correspond to some parameters for some processing.
And you have another model using these parameters.
You may think Foreign Keys on the second model.
But I saw a hack where some "ids" were not in the first model.
In that case, these ids were small negative constants and the positive ids were true foreign keys.
When I saw that hack, I thought about a way to normalize this, by adding "frozen rows" with code and migrations necessary for that, for the small negative ids. Technically, you can still have all the related code hardcoded for these constants and avoid to fetch rows in database, even if the rows now do exist.
I think all uses that were already in the code when I learned the hack were patchable like this.
This is somehow too far memory at the moment.
I have extended this in some cases by small negative constants that do not correspond to a possible row in the first table, but more or less to subsets of such rows.
But somehow these subsets can be linked to a third model and handled by another Foreign Key.
I did not submit a ticket at a time were I had a clear view of this approach of "extended foreign keys", but I remember that I truly saw a gain with that and I was already seeing the possibility of using "more foreign keys or insert more frozen rows in some tables".
Somehow I still see a gain in some use case, but I'm not sure it was the main reason I was seeing at the time.
Imagine your second model has two real foreign keys FK1 and FK2 giving some "context", and then 2 FKs for some processing parameters FK3 and FK4 and FK3 and FK4 are extended foreign keys with 2 constants like "see context given by FK1" and "see context given by FK2".

Technically, you could solve the problem by FK1, FK2, FK3 not extended, FK1.3, FK2.3, FK4, FK1.4, FK2.4,

or more cleverly FK1, FK2, FK3, Boolean 3.1 (use context FK1), Boolean 3.2 (use context FK2), FK4, Boolean 4.1 (use context FK1), Boolean 4.2 (use context FK2),

or more cleverly FK1, FK2, FK3, FK3Complement TINY INT (use context FK1 or use context FK2), FK4, FK4Complement TINY INT (use context FK1 or use context FK2)

But even in "best case" with FKComplement you're adding 2 columns for almost nothing.
I gave the other possibilities because I think maybe in some cases I had to do something trickier.
There was 2 other reasons if I remember well :

• NULL values for columns had a bigger penalty in MySQL that how it is implemented in PostgreSQL that I use now (I may be wrong),
• complicating the code creating the SQL request to handle the case of NULL values was totally useless and a waste of time.

So, maybe this is a micro-optimisation, a technical hack, but extended foreign keys for signed integers were 0 correspond to NULL somehow, positive ids are tested for the foreign key constraints and negative ids are not tested for foreign key constraint and handled by the applicative code does improve the performances and the simplicity of some code.
It may not be a huge win, but sometimes it helps and there may be other gains that I don't remember.
So I think it is a quick win, because it should be a matter of hours to add [EXTENDED] FOREIGN KEY in the SQL parser, a flag for that on the constraint, and an if return to not enforce the constraint if id <= 0.

Best regards,
Laurent Lyaudet