Solved

SQL PrimaryKey best practice

Posted on 2011-03-16
5
259 Views
Last Modified: 2012-05-11
I am creating a table ProjectSites that will be a child table to another table Projects and it will only have three columns, here is the current schema

ProjectSites Table:

ProjectSiteID          identity  pk
ProjectID                int          fk
SiteID                     int          fk

So the idea is, there can be one or more Sites assigned to every project. There will never be a case where the same Site is assigned more than once to the same Project. My question is this, Should I make the PK of the table a combination of all three columns so that no mistakes can ever be made?
0
Comment
Question by:AkAlan
  • 2
  • 2
5 Comments
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 333 total points
ID: 35149761
Yes that's the only way to meet all your conditions.
0
 
LVL 6

Author Comment

by:AkAlan
ID: 35149770
Ok, I thought that was so, I was just thinking about any performance issues that could cause. Thanks.
0
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 333 total points
ID: 35149796
Will it impact performance? yes but  marginally given they are int columns. It is unavoidable given the requirements specified and best practice
0
 
LVL 10

Accepted Solution

by:
Asim Nazir earned 167 total points
ID: 35149831
You can also Add Unique constraint to two of the fields i.e. other than one PK field.
0
 
LVL 6

Author Closing Comment

by:AkAlan
ID: 35162240
Thanks to both.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question