|
|
|
|
|
|
by mpolichette
1632 days ago
|
|
|
I'm with you on this one. I prefer it to be in your face simple to understand over the terse-ness. In the gist example, I actually prefer the SQL-92 approach where we are joining given an explicit comparison condition. Every other implementation seems to be trying to hide details, for what gain? Less typing? In order to use FOREIGN, you will need to know not just what columns a table has, but also their configuration. Which would also require that you have properly configured your tables. While this shouldn't be a hard ask, it does add additional dependency and makes use of this "tool" slightly less "portable" between systems. I have unfortunately seen cases where people will only have foreign keys un-enforced by their table config. As a dev, if you're introduced to a new DB, you wont know immediately if you can use this, and if things are configured wrong, you need to make a pretty significant change to be able to use it. I don't see a lot of harm from adding this syntax however as people are free to not use it and it relies on an existing strict convention. |
|
|
This is a good argument I will add to the list.
Also interesting to read about un-enforced foreign keys. I haven't used MSSQL myself, the DB in which I heard it's possible, I've only been using PostgreSQL for the last 20 years, and before that MySQL.
I think the problems you describe is an argument against a WITH NOCHECK feature, since it could be misused. Maybe it's necessary in some databases still, but at least in PostgreSQL, the FOR KEY SHARE lock solved all the issues with concurrent updates we had at Trustly. The FOR KEY SHARE was a huge patch [1] written mainly by Alvaro Herrera. Thanks to it, Trustly has never since had any performance problems with foreign keys, and they have AFAIK not needed to drop any foreign keys up until today due to locking/performance problems.
[1] https://www.commandprompt.com/blog/fixing_foreign_key_deadlo...