SQLServerCentral / Discuss Content Posted by Ravi Lobo / Article Discussions / Article Discussions by Author / Foreign Keys / Latest Posts

RE: Foreign Keys

naveenreddy.84 — Thu, 20 Aug 2009 18:25:59 GMT

Hi, Can you help me out in updating such Foreign Key relationships. In one of my tables, i have a FK relationship refering to my primary key of the same table.The Table called MacMaster has following columns:MacID MacName ClusterIDthe ClusterID here is a foreign key referencing the MacID(PK) of the same table.MacID(identity column;PK) MacName ClusterID1 Mac1 22 Mac2 NULLI was trying to load this table from an another table having following columnMacName ClusterNameMac3 Mac4Mac4 NULLMac5 NULLKindly suggest me a method to load the above data to MacMaster Table.Finally my MacMaster table should have something like this:MacID MacName ClusterID1 Mac1 22 Mac2 NULL3 Mac3 44 Mac4 NULL5 Mac5 NULLIs this can be achieved through TSql or do i need to create a some kind of front end for it.Kindly SuggestRegards,

RE: Foreign Keys

balaji_rcs — Thu, 01 Nov 2007 03:02:45 GMT

Hi, This is really a good article. I had an doubt about that i want to generate the insert statement script for moving the data from one db to another. Nearly 500 tables are there, i want to know how to prioritize the tables list. Becuase each and every tables are internally referenced. Can you please help out to fix the issue

RE: Foreign Keys

nogoodboyo — Mon, 11 Jun 2007 22:24:00 GMT

No mention of trusted FK's? See http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx Handy to know, especially if you are lumbered with a db that exposes all data through very complex (and poorly performing )views!

RE: Foreign Keys

Vic Kirkpatrick-173212 — Thu, 31 May 2007 05:48:00 GMT

To not use FKs in an OLTP with any level of complexity is just plain ignorant. There is no excuse for it, and you will just get badly burned. Amazingly enough, through my career I've had to fight developers, managers, and even other DBAs in order to install FKs on databases! It's incredible. In almost every case, they have grown to see the rewards by catching logic / data problems in testing with the FKs. Not to mention happy customers...

RE: Foreign Keys

Ravi Prashanth Lobo-275382 — Thu, 20 Jul 2006 06:25:00 GMT

Thank you all for your suggestions!

RE: Foreign Keys

bitbucket-25253 — Wed, 14 Jun 2006 07:16:00 GMT

As usual I am a day late and a dollar short, but heck was a good, clear, concise tutorial on foreign keys.

Thanks for the effort you put into this ... and as suggested by some one else, would be nice if you took the challenge and attempted to 'explain' the many to many relationship.

Again - thanks

RE: Foreign Keys

Ian Yates — Sat, 03 Jun 2006 00:40:00 GMT

Fair comment from Roger. Makes me think actually that the next step for the article would be many-many relationship as many people get that confused

RE: Foreign Keys

Roger Thomas — Fri, 02 Jun 2006 13:04:00 GMT

Good, terse explanation.The only issue I'd have with it is the use of the terminology "Parent" and "Child" to describe the relationship between the primary key and the foreign key(s) that reference it. In the best methodologies I've seen, the terms "parent" and "child" are reserved for unusual situations where you have things that depend for their existence on other things. An example of this might be a line on an invoice - the invoice must exist for the line to exist, do the invoice would be the "parent" and the line the "child". With dissimilar things, such as drivers and racecars, you'd have two distinct entities that were related by a foreign key, but neither would be the "parent" of the other.That's terminology, but it avoids confusion.

RE: Foreign Keys

Deepak Patil-309214 — Thu, 01 Jun 2006 11:39:00 GMT

 If you don’t have a key, you don’t have a table!                       - Joe Celko

It is very nice article                 which one can easly understand                 without deep knowlege of MSSQL.

Also basic concepts are explained very well !!!!!

RE: Foreign Keys

Mike C — Thu, 01 Jun 2006 08:06:00 GMT

I think General Patton said: "Fixed fortifications are a monument man's stupidity." Same could be said for "generic" database applications.

RE: Foreign Keys

Ian Yates — Thu, 01 Jun 2006 03:45:00 GMT

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!

RE: Foreign Keys

Fal — Wed, 31 May 2006 17:19:00 GMT

"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.

RE: Foreign Keys

SL10 — Wed, 31 May 2006 15:42:00 GMT

"Recently I came across a highly transactional DB with no FK's! The data integrity was managed by the front-end application."Another reason for this happening is that vendors try to hide proprietary information architecture. No referential integrity, combined with generic object names and procedures/function definitions created on one line. (Great Pains first comes to my mind.) With setup like that, it is really painful to "untagle" relationships at the backend.

RE: Foreign Keys

Jason-262847 — Wed, 31 May 2006 12:48:00 GMT

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.

RE: Foreign Keys

Aaron Ingold — Wed, 31 May 2006 12:30:00 GMT

Recently I came across a highly transactional DB with no FK's! The data integrity was managed by the front-end application.Unfortunately this is not uncommon in database / application design. I can't tell you the number of vendor-supplied applications I've looked at that don't make use of basic database features such as keys, indexes or constraints. Not to offend anyone, but it usually happens when you've got front-end developers driving the whole thing without a good DBA adding input. When I came to my current position, the developers were running the show and I had to slowly bring them over to agreeing to use things like stored procedures, constraints, keys and db-side functions.Also, while there are many things I have fundimental disagreements with Celko on, I do like that quote as well.Good article.

RE: Foreign Keys

Ravi Prashanth Lobo-275382 — Wed, 31 May 2006 06:19:00 GMT

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….

RE: Foreign Keys

geovar — Wed, 31 May 2006 04:07:00 GMT

Interesting article. Most of things are well known, but here they are summarized and there are also some interesting ideas.

The most curious for me was this one:

Recursive

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.

RE: Foreign Keys

Mike C — Tue, 30 May 2006 21:45:00 GMT

You got my vote In that Celko quote, I think he was specifically referring to a Primary Key.

Foreign Keys

Ravi Prashanth Lobo-275382 — Wed, 24 May 2006 08:05:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rlobo/foreignkeys.asp