- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
When I frist read it (incorrectly), I thought it was referencing a Listings table, and I thought "Oh boy, it's another key-value table!".
Admin
"Rational database" "Standard" That made me giggle :-).
Admin
This must be that "mirrored data" concept I've heard about.
Admin
I think anything related to databases are where I've seen the most WTFs to this day
Admin
That foreign key is so efficient, it eliminated the need for cascade on delete.
Admin
Looks like the developer understands how constraints work but has no idea what primary keys do.
I wonder if it's possible to add a record to this table? I mean ... if the value of ListingDetails.Id doesn't already exist in the table, this constraint would prevent inserting a new record, wouldn't it?
Or, maybe that was the point -- to make the ListingDetail table immutable? So he also doesn't understand what making a table read-only does.
Admin
I wondered the same thing myself, but apparently the check is done after the insertion (or, more likely, as if the insertion h
Addendum 2023-03-29 11:32: ugh. as if the insertion had been done, to avoid unnecessary increments of autoincrementing id columns.
Admin
I tried adding a foreign key like this to a MySQL table, it got an error
So maybe only SQL-Server allows this WTFery.
Admin
Gotta love those code smells that aren't just your normal smells, but more like the ham sandwich that fell behind the desk and onto the radiator three months ago.
Admin
Or, you could just leave it alone and it would work automajikally: If a record exists with the same key, it can't add so there's no autoincrement. If there is no existing record with the same key, then it can insert and the autoincrement stuff works. It's not rocket science. It is complicated. It's how databases work, and I know you understand that fully.
idunno ... i use Oracle, and it doesn't allow this WTFery, either. There's a lot of things people say about The Big Red O, but they do know WTF they're doing. There's a chance Oracle fixed this when they acquired MySQL, but I sincerely doubt that since this stinks of MS-WTFery and I'd bet there's a greater chance that MSSQL is the only database that allows a table to have a foreign key constraint against itself.
Also ... it's been 40 years since I've seen software that builds FK constraints into the database; most of what I've seen has that built into the programs so that dropping and rebuilding tables doesn't violate constraints on foreign keys. (Consider a modification to add a column to a table that creates a temp table, inserts all the records from the original table, updates values in the new column based on some conditions, drops the original table, and then renames the temporary. This would take a ridiculous amount of dropping constraint dependencies and rebuilding them. Or, the program can just check if the department code on an employee record actually exists in the department table.)
Admin
Nope, just tried it in PostgreSQL and it also allows a table to have a foreign key relationship against its own primary key (probably because tables can have constraints against themselves, and it's more bother than it's worth to prevent the foreign key from being the primary key).
It also allows insertions and deletions to go ahead (which, given that it allows such a thing in the first place, makes sense since the table would still be consistent after either change). So the constraint remains completely spurious.
Admin
This might be not a TWTF but just a silly mistake if one tried to create one-to-one relationship from ListingDetails to Listings and accidentally put the same table name twice.
Admin
Yes it does (19c):
SQL> create table t (id number primary key);
Table created.
SQL> insert into t (id) values (1);
1 row created.
SQL> alter table t add foreign key (id) references t(id);
Table altered.
SQL> insert into t (id) values (2);
1 row created.
SQL> select * from t;
Admin
Self-referencing foreign keys are part of all SQL standards, while it makes no sense to refer a column to itself, it is also a good test how standard compliant your DB is. I know that MS SQL and Postgres follow the standard, not sure about DB/2 and MySQL (haven't used both in a decade), I'm pretty sure it will not work with Oracle because that DB is all but standard compliant and everyone by now should have switched to Postgres anyway.
Admin
And I stand correct while I was posting, even Oracle seems follows the standard this time :-)
Admin
Mine doesn't -- maybe it's in a package that I don't have installed. Hmmm.....
Admin
You may not be seeing constraints in DBs but my firm is strongly pro-data integrity. It's a small firm though and mostly building typical CRUD apps over and over. What field do you work in?