Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
 Posted - 2009-10-04 : 11:48:42
|
I am not very familiar with all that foregin keys have to offer.What I want to do is have a primary key tableMYTABLE1(PKID int,PKVAL varchar(5))Then a foreign key tableMYTABLE2(FKID int,PKID int,PKVAL varchar(5))The relationship will be on pkid and pkval. What I want to happen is when I update PKVAL in MYTABLE1, for it automatically to update PKVAL in mytable2 so it matches. I know how to do this by coding it out, but what's the proper way?
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-10-04 : 13:25:07
|
| Foreign key relationship with cascading update. Check Books Online, I think it'll be under ALTER TABLE (specifically ALTER TABLE ... ADD CONSTRAINT ...)--Gail ShawSQL Server MVP |
 |
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2009-10-04 : 20:23:06
|
OK,I seem to have a fairly good understanding now of foreign keys. How much do foreign keys impact inserting data performance wise in general? I can have thousands of individual inserts coming in each minute, so is there any significant performance infringment by using a FK constraint over not using one? In the past I use to just keep the data design simple and limit all insertions to stored procedure with tested code in order to "HOPFULLY" keep data integrity, and was not using FK restraints. I was informed that this was a bad practice, so I am trying to improve my coding and jus want to be made aware of everything that the FK relationship will now change in my db.Thanks
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-10-04 : 22:24:11
|
quote:
How much do foreign keys impact inserting data performance wise in general?
The only way to know is to measure before and after adding them.quote:
I can have thousands of individual inserts coming in each minute, so is there any significant performance infringment by using a FK constraint over not using one?
Again, you'll have to measure. "Significant" is a subjective term, and there are tons of factors that affect performance (RAM, disk speed/layout, other hardware features)quote:
In the past I use to just keep the data design simple and limit all insertions to stored procedure with tested code in order to "HOPFULLY" keep data integrity
If your procedure code was querying tables to ensure proper data relations on insert, then a foreign key will reduce/eliminate the need for that, and likely perform as well or slightly better. If your code DID NOT query tables, your data integrity was limited or non-existent. The biggest difference a foreign key will bring to your code is that it will, upon violation, throw errors that you may not be able to catch or work around. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-10-05 : 03:10:49
|
| Plus, in some cases, foreign keys can make the reads faster as the optimiser can use the additional info to generate better execution plans.To add to what Rob said, I've seen cases before where people said they didn't need foreign keys and would enforce integrity with code. Without exception there were data integrity problems when the data was closely looked at.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Normalized data with foreign key.