Does adding Primary and Foreign Keys to an existing database mess with your SPs?

Question

Does adding Primary and Foreign Keys to an existing database mess with your SPs?

ibzmiller

Old Hand

Points: 312
More actions
July 12, 2010 at 3:11 am

#235095

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 1

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.

ibzmiller Old Hand Points: 312 More actions · Answer 2

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?

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 3

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?)

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

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

ibzmiller Old Hand Points: 312 More actions · Answer 5

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.

ibzmiller Old Hand Points: 312 More actions · Answer 6

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Wingenious SSCommitted Points: 1648 More actions · Answer 9

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.