July 12, 2010 at 3:11 am
Hi Guys,
I've just inherited a SQL Server 2005 database that doesn't have any primary or foreign keys set, even though the relationships exist.
My question is, can i just add the primary or foreign keys to the database or would I need to amend all the SPs?
There are 148 tables, 32 views and over 700 Stored Procedures so I'd rather not have to change them all just to get a bit more structure.
Any help you can give guys would be greatly appreciated,
Iain
July 12, 2010 at 3:52 am
Hi Iain,
This is a good question. Generally, stored procedures shouldn't have any effect on key relationships, unless they've been coded in an odd way. Step #1 - is the database supported by a vendor? If so, I wouldn't touch it. I would recommend that you perform a validation to verifying the data's integrity - but remember that what you think might be the primary key may not always be.
Foreign key relationships can be tough, as stored procedures may break if, for example, the child rows are inserted before the parent.
Unless you have the development team in-house, I think the amount of effort and time required may not give you a great benefit, and the risk may be too high. You may be better off monitoring the data validity (see earlier step), and reporting on that regularly, and let the application maintain the integrity.
July 12, 2010 at 4:26 am
Thanks for that Jim. Yeh thought that might be the case. I Was hoping that there was a magic wand out there.
It's not a vendor db just an old one. It has just never had any proper db management. So I guess I'll have to live with it.
The validation you spoke of is that just a manual process or is there a tool?
July 12, 2010 at 5:28 am
Unfortunately, there's no magic wand, but you might find, after monitoring the data integrity for some time, that you understand the business rules and the database structure sufficiently to attempt to put the constraints on, and be reasonably sure things will be OK.
I don't know of any tool - it would be more of a manual process. Examine a table, determine what you think are the unique keys, and then validate that logic. Then identify which tables relate to this key, and make sure there are no orphaned rows.
Another thing you could try is to capture all stored procedure executions for a period of a few weeks, create a new database with your constraints on, and replay the workload. This will give you a good coverage of which stored procedures are run regularly, but there may be some that aren't run frequently, or that can occasionally have odd parameters.
After having both a workload to test with, and a good understanding of the data, you might then be in a position to put on some permanent constraints. Again, the question is whether it's worth it from a business perspective (How important is it that this data is never wrong? What will it cost the business if it is? What is the effect if a transaction fails because you had a constraint on the table based on an incorrect assumption about the data?)
July 12, 2010 at 7:11 am
Another consideration, adding PK's means you're adding an index, clustered or nonclustered, to a table where none previously existed. That will affect performance, probably positively, but possibly negatively.
You have tons and tons of testing in front of you.
Are there indexes in the system, especially clustered indexes?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 13, 2010 at 4:17 am
Hi,
Yes plenty of testing ahead. No I don't think there are any clustered indexes. It has just been a matter of, theres a table. No consideration on how it will work.
July 13, 2010 at 4:25 am
It's looking like a long road ahead. Jim I'll try out your validation idea, it sounds like my best option.
Thanks guys for all of your help, much appreciated. 🙂
July 13, 2010 at 5:50 am
ibzmiller (7/13/2010)
Hi,Yes plenty of testing ahead. No I don't think there are any clustered indexes. It has just been a matter of, theres a table. No consideration on how it will work.
While you have work in front of you, I think you'll find that in 99% of the work you do, you're going to get a massive improvement in performance. Take your time, test a lot, but you're going to be a hero on this one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 13, 2010 at 9:42 pm
I agree with the others. Things could break if the DRI is violated or PKs aren't created using IDENTITY, etc.
My recommendation would be to do the same thing as if you were troubleshooting a performance problem... find the slowest running queries whether they be from the GUI or SPROC, do a brief analysis to determine the tables they affect, use profiler to find all queries that affect those table and do a bit of a check on those to see if they're contra FK order sensitive. Other people can certainly help in that area.
Then, add the PKs and see if anything breaks (and, depending on how inserts and updates are done, they certainly could) over a week or so. Then, add the FK's... one FK at a time. I probably wouldn't add more than a handful of tables' FKs in a 24 to 48 hour period.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 14, 2010 at 1:20 pm
Hopefully the database does not contain any single "foreign key" referencing multiple parent tables, where the "foreign key" column(s) contains values that come from one of several different parent tables, depending on the value of another column. I have come across this approach an alarming number of times recently.
Creator of SQLFacts, a free suite of tools for SQL Server database professionals.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply