November 5, 2008 at 3:53 am
Dear All
I have a table Pages and it has the following fields
PageId smallint
PageName nvarchar(250)
PageTitle nvarchar(250)
fk_buildId int
Now normally, how I do it, set the PageId as int and make it the primary key with autoincrement.
However, someone suggested to me to put a combined primary key of PageId and fk_BuildId and set the PageId to smallint, and reset to zero whenever the fk_buildId changes. This is done to avoid the PageId being an int.
What is the normal standard of doing this? 1 PK with autoincrement or a combination of 2 PK's? I am asking since to keep a comb of 2 PK's requires more work and inserts a little bit of more complexity to it.
Thanks for your help and time
Johann
November 5, 2008 at 4:09 am
Johann Montfort (11/5/2008)
Dear AllI have a table Pages and it has the following fields
PageId smallint
PageName nvarchar(250)
PageTitle nvarchar(250)
fk_buildId int
Now normally, how I do it, set the PageId as int and make it the primary key with autoincrement.
However, someone suggested to me to put a combined primary key of PageId and fk_BuildId and set the PageId to smallint, and reset to zero whenever the fk_buildId changes. This is done to avoid the PageId being an int.
What is the normal standard of doing this? 1 PK with autoincrement or a combination of 2 PK's? I am asking since to keep a comb of 2 PK's requires more work and inserts a little bit of more complexity to it.
Thanks for your help and time
Johann
The subject of using natural keys or surrogate keys (e.g. identity, GUID, etc’) can cause a huge debate. Personally I’d go with your first approach. I’d use an identity and make it the primary key, but there are other DBAs that don’t agree to this method.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 5, 2008 at 4:36 am
Johann Montfort (11/5/2008)
Dear AllI have a table Pages and it has the following fields
PageId smallint
PageName nvarchar(250)
PageTitle nvarchar(250)
fk_buildId int
Now normally, how I do it, set the PageId as int and make it the primary key with autoincrement.
However, someone suggested to me to put a combined primary key of PageId and fk_BuildId and set the PageId to smallint, and reset to zero whenever the fk_buildId changes. This is done to avoid the PageId being an int.
What is the normal standard of doing this? 1 PK with autoincrement or a combination of 2 PK's? I am asking since to keep a comb of 2 PK's requires more work and inserts a little bit of more complexity to it.
Thanks for your help and time
Johann
Having two primary keys is somewhat impossible as only one will work as a primary and another will work as secondary at the same time.
It depends on your requirement that what column you want to make primary but dont forget to create indexes.
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 5, 2008 at 4:48 am
If you had another table with the PageID as an identity column and had a FK to this other table, the composite key on your table would make sense.
In your particular case, using a composite key is probably just going to be inconvenient. First, to get a unique record you will always have to filter by two columns rather than one. If you have another table with an FK to this table, you will need two columns in the other table for the FK relationship rather than just one. If you make the primary key the clustered index on your table, adding records could fragment your index. Finally, you would have to come up with your own way to manage coming up with the correct PageId for a new record - and many home-grown identity type solutions seem to have concurrency issues and perform poorly.
I would obviously lean toward using a simple identity field in this case.
November 5, 2008 at 5:11 am
Hi SSCrazy
I have another table like this
BuildId
BuildName
BuildURL
So in that case it makes sense to have a composite key?
November 5, 2008 at 5:15 am
If you also had a table with a primary key of PageID, the composite key on the table with both would make a lot of sense and be necessary.
November 5, 2008 at 5:18 am
Michael Earl (11/5/2008)
If you also had a table with a primary key of PageID, the composite key on the table with both would make a lot of sense and be necessary.
Oh so you mean 2 tables with a PageId as primary key?
No I do not have that, every primary key that is not natural is always unique to that table
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply