You got my vote In that Celko quote, I think he was specifically referring to a Primary Key.
The most curious for me was this one:
I can not imagine any practical use of below code, but theoretically it is possible.
CREATE TABLE master( pkey int PRIMARY KEY FOREIGN KEY (pkey) REFERENCES master(pkey))
It really does nothing. I hoped it will prevent update of PK and deleting a record, so I tried it, but unfortunately it doesn't prevent either PK update nor record deleting.
You got me there!
Yes, Celko is referring to primary keys.
That was my favorite quote. I wanted to have it in my first article. Hope it will not harm someone….
I really liked the script that will traverse the FK hierarchy. However, it looks like it will only work as long as there is no recursiveness - if a table has a FK within it to itself, the code will loop forever trying to traverse the same table over and over again.
"Recently I came across a highly transactional DB with no FK's! The data integrity was managed by the front-end application."
I've seen this also. The ERP system was designed to be "generic" - any database platform, on any OS, for any customer. As customers all have differing requriements, an FK for one might be detrimental to another, so all were left off.
And then, as the "customer", we paid a fortune getting the application customised to check the data integrity we required. A nice little earner, I must say.
Good article - I too have come across some rather powerful systems with no FKs. Their excuse was that when data loading they could do it in any order. I might send them your quick and easy code to disable and enable all constraints.
Hope this article influences some folk to ensure they use ref integrity!
I think General Patton said: "Fixed fortifications are a monument man's stupidity." Same could be said for "generic" database applications.
