October 15, 2002 at 3:47 pm
Quick question: is it a good or bad practice to have a PK made by 2 columns? Beside these columns, no other column can uniquely identify a row. These two are used often in the queries.
Thx.
October 15, 2002 at 3:56 pm
It is by some considered bad and they would opt for a INDENTITY column. I and many others however prefer not to waste space and indexes on something we want to control. The key is make your first column in the primary key index the one with the most unique number of values so that stats will favor the index more often. It also means you can keep the data unique without creating an extra constraint to do so. However, if you are going to create foreign key constraints against it you may well want to use an IDENTITY column instead to limit duplicated data, just make sure you add a unique constraint to protect yourself against repeated data.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 15, 2002 at 4:47 pm
Let say we have a table named RATE:
Region Area Rate
North A 100
North B 110
West A 250
West B 255
Region and Area will make a PK defined by Region first and Rate second. Both Region and Area are FKs referencing 2 columns in 2 other tables. To search for a rate in a certain area, we will search WHERE REGION ='' AND AREA = ''. Adding extra IDENTITY column is not helping. I am sure the uniqueness of the 2 columns make it good candicate for PK. The thing I'm confused is would it be good when that 2 columns are FK.
Thx.
October 15, 2002 at 6:10 pm
Is the rate can potentially change then it is not considered a good idea to use such a PK-FK relationship. In you case I would create an int column set to IDENTITY and then on the other tables reference the int column, this insures entity integrity and saves some space. Now if the rate column never changes then 2 columns is fine aznd does not cause any issue as long as your app insures on the other tables when inserts occurr.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 15, 2002 at 6:15 pm
thank you.
October 16, 2002 at 6:45 am
I would add that when you get above 2 columns and are using FKs then you will be giving yourself alot of heartache by using composite keys, more data, more complex queries.
The other point is the length of the PK. identities are preferred due to it generally being 4bytes, If you are using strings in the primary keys then you index length becomes longer which if not used in Fks is not too bad but when used as FKs really hurts performance.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply