Primary keys...

[Neil Toulouse]

Hi folks!

I am using SQL Server simply as a database engine (as in all the referential integrity etc is handled by the app). I have migrated some data over from an old application, and whilst the tables of this application have primary keys, they are not set as 'primary key' in SQL.

I do not want to make any structure changes to this data until I have checked it out, so my question is, what are the downsides of not having the primary key set?

TIA
FatSlug

I like work. It fascinates me. I can sit and look at it for hours...

 

[SQLDenis]

>>what are the downsides of not having the primary key set?


Data integrity is one, what if someone modifies the data from query analyzer

Or what happens if 2 users at the same time from your app do an update and enter the same value (did you account for that scenario?)

performance when you have a PK a clustered index will be created which will help in speeding up retrieval of data

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[Neil Toulouse]

Data integrity etc is all controlled by the app so there are no issues there. It's not that I won't create the PK's at some point, just not now.

So can I safely say the main downside would be a performance hit?

TIA
Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[gmmastros]

Performance benefits alone should be worth the effort of creating primary keys.

But, like Denis says, there are data integrity issues. Think of primary keys as seat belts in your car. If you drive properly, you'll never need them. However, it's possible that you could get in to an accident where the seat belt may save your life. That slim chance is well worth the effort of wearing a seat belt every time you get in your car.

Primary keys are the same way. If you code your application properly, you can along along fine without setting primary keys. Unfortunately, you (or someone else) may decide to add some functionality later that isn't as careful as it should be. This same logic holds for foreign keys and other constraints.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

Also,
Tables without primary keys are heap tables. Look that term up! They make database maintance very difficult. It becomes very difficult to clear up data fragmentation. If you create other non-clusterd indexes for performance you can not rebuild them when the become dirty.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Neil Toulouse]

Thanks for the info guys! It's not that these tables don't have PK's they are just not set as PK's in SQL at the moment. This is only a temporary issue (the legacy data has managed to get duplicate values in the PK field in a few of the tables and I need to check them all out before I set them up as PK's), but was just wondering if I could let the users look at the data without too many issues.

I don't intend to run the system in full without PK's so don't panic

Neil

I like work. It fascinates me. I can sit and look at it for hours...