PK Questions

Question

PK Questions

Ken at work

Hall of Fame

Points: 3426
More actions
January 29, 2010 at 8:36 am

#229520

This may seem basic to those on this forum, sooooooo, I apologize for the simplistic question, though it's not to me.
Example:
Table: tbl_Contract
Fields: - Bold indicates fields that together make a unique contract - Yes everything can remain the same and there can be a new effect date.
Contract_Name
Contract_Number
Contract_Date
Contract_Dollar_Limit
County
State
Table: tbl_Jobs
Fields: - Bold indicates fields that together make a unique job - Yes the same job can begin (restart) under a new contract.
Company
Main_Job
Sub_Job
Contract_Name
Contract_Number
Contract_Date
Contract_Dollar_Limit
Job_Address
Job_City
Job_State
Job_Zip
etc...
and so forth and so on, multiple tables, etc... some linked to contract, others to the job, and from them linked, etc...
Question: What is the right way to do the following?
A) Should I add an Autonumber field (bigint) and use that as the PK? Clearly it would reduce the amount of fields I have to repeat in the tables that use this as an FK. But!!! If I do use an Autonumber field as the PK then how might I prevent the other fields that really should be unique from be accidently repeated, i.e. Contract_Number, Contract_Date, Contract_Dollar_Limit.
B) Use the unique fields as the PK. This of course will keep (require) them to stay unique (yeah!!!) but will also cause them to be repeated in the foreign tables. Less, normalization (if I understand correctly).
Could someone help someone being plunged into SQLServer? I'm okay with terms, databases and have created several with MSAccess and VB front ends. However, this is bigger that I am used to. Yes there are several more tables that rely on these keys...
Thank you again, is that right?
How about thank you in advance,

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

Ken for me, what i end up doing is still creating a PK on an identity column, ie "ContractID",

and having a unique constraint on the columns that make a unique contract...the unique cosntraint does what you were after, preventing duplicates.

i much prefer having a table ContractDetails, for example, refer to ContractID as a foreign key, instead of a 4 column FK to the PK: some of my developers would get confused with a complex foreign key like that.

so i would do something like this:

CREATE TABLE Contracts(

ContractID int identity(1,1) primary key,

Contract_Name..

Contract_Number..

Contract_Date..

Contract_Dollar_Limit..

County,

State,

CONSTRAINT UQ_Contracts UNIQUE(Contract_Name,Contract_Number,Contract_Date,Contract_Dollar_Limit)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ken at work Hall of Fame Points: 3426 More actions · Answer 2

Thank you,

I will read up on unique constraints.

Looks like it'll work great.

Thanks again,

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

I would follow Lowell's advice. You need a unique constraint to ensure your data is intact, but there's no need to copy all that data over to a FK table.

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 4

Steve Jones - Editor (1/29/2010)
I would follow Lowell's advice. You need a unique constraint to ensure your data is intact, but there's no need to copy all that data over to a FK table.

But, there is also nothing wrong with using a multi-column key. In some cases, you'll want those columns in the child tables to help with queries on those tables.

For example, in this situation - if we use an identity for the PK and set the FK to that value then what is the query to get the job details and include the contract name?

SELECT c.ContractName, j.*

FROM dbo.Jobs j

INNER JOIN dbo.Contracts c

ON c.ContractID = j.ContractID

If, however - you are using the multi-column key, you only need to query the Jobs table. The contract information is already included unless there are additional elements from the contract that you want to include.

Granted, it can become quite a mess if you have a lot of columns defining the key. But, whether or not you use a multi-column key or an identity shouldn't be an arbitrary decision. You need to understand the system and how it is going to be used before you decide one way or the other.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 5

There isn't necessarily anything wrong with a multi-column key, I agree. Strong RI declared will keep things in sync, but I find having all those columns in all tables cumbersome. Especially if there are edits to the PK, which can happen. You'll cascade a lot of stuff that you don't necessarily need to.

It's a balancing act. If it's few tables, I might leave them as multi-column keys if they are queried often. If you don't necessarily query all those fields, I'm not sure. Your indexes and everything grow larger as well, which may or may not affect your query performance. You'd have to test to know if it's significant for your application.

WayneS SSC Guru Points: 95460 More actions · Answer 6

Steve Jones - Editor (1/29/2010)
Especially if there are edits to the PK, which can happen.

Ideal world: the columns that define a PK will NEVER, EVER be changed.

Real world: invariably, some condition will crop up that will require a change. Or you'll identify another column that should be part of the PK... this could throw your whole database design "loopy".

So how to handle it? Use your identity field as the PK, and a unique constraint on the columns that make up the "natural" key. Then, WHEN something changes, it won't be as big a deal.

Edit: I know some database designers that always assume that they didn't get the complete specs from the customer / primary stakeholder, and they always use identity columns as primary key constraints with unique constraints on the columns that define the natural key. Even though I understand and agree with the argument of using the natural keys as the primary keys, this approach definitely makes modifications easier... which means less $$$... which is always good for the customer.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

I normally do what Elliot suggested.... I use an IDENTITY column as a PK even if a natural key is available. It just gives more choices and in this age of desktop Tera-Byte disks, the utility it occasionally provides overrides the fact that it takes a little extra space (4 bytes, normally. Only adds about 40 MB to a 10 million row table). Of course, the natural key would be defined as an AK using a UNIQUE INDEX on a non-nullable set of columns.

Note that I didn't say that I always use the IDENTITY column as the clustered index. If the order of the AK makes sense for the table, the AK may be the clustered index.

To me, what makes sense for the clustered index depends on whether the table will be used mostly by SELECT or by INSERT or a combination of the two. If the table is large and receives a large numbr of inserts, then I'm very likely to put the clustered index on the IDENTITY column to prevent rampant page fragmentation. If it receives few INSERTs, then I may put the clustered index on the AK or some other column(s) to support the most common queries.

Bottom line... "It Depends" on how the table is used.

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