Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
takemyjazz
Starting Member
3 Posts |
 Posted - 2006-12-01 : 11:20:27
|
| Hi.I have a table with two primary keys, say "id" and "version".I want the "id" field to behave like an Identity field, i.e. I want it to have a sequential numbering. The "version" field is another number that I want to insert manually.What is the best way to make inserts to this table?I could do this:SELECT @id =(isnull(max(id), 0) + 1) from TableAINSERT INTO TableA(id, version, description)VALUES (@id, @version, @description)Is there a better way to do it?Best regards. |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-12-01 : 11:26:00
|
| Yes. The better way to do it would be to just define ID as an identity column...STAR SCHEMAS ARE NOT DATA WAREHOUSES! |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-12-01 : 11:47:49
|
| no table can have 2 primary keys.....but a table can have 2 unique keys (one of which can be the primary key).the point of your specific example however defeats me as it just creates a 2nd column with a value which is a fixed offset from the primary key....a bit pointless really. |
|
|
|
takemyjazz
Starting Member
3 Posts |
Posted - 2006-12-01 : 11:55:03
|
quote:
Originally posted by AndrewMurphy
no table can have 2 primary keys.....but a table can have 2 unique keys (one of which can be the primary key).the point of your specific example however defeats me as it just creates a 2nd column with a value which is a fixed offset from the primary key....a bit pointless really.
It's not two primary keys, it's one primary key composed of two fields.The second column is not pointless, it makes sense from this project point of view. The point basically is that you have one entity but you can have multiple versions of that entity. So, I can't have a single field primary key. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-12-01 : 12:23:02
|
quote:
Originally posted by takemyjazz
quote:
Originally posted by AndrewMurphy
no table can have 2 primary keys.....but a table can have 2 unique keys (one of which can be the primary key).the point of your specific example however defeats me as it just creates a 2nd column with a value which is a fixed offset from the primary key....a bit pointless really.
It's not two primary keys, it's one primary key composed of two fields.The second column is not pointless, it makes sense from this project point of view. The point basically is that you have one entity but you can have multiple versions of that entity. So, I can't have a single field primary key.
YOu did say that you need two primary keys originally .... Anyway, if what you are saying is true, then NEITHER column should be an identity. Sounds like you need two tables -- one table for your entity, with maybe a PK of an identity, and another table that contains versions of that entity, with a composite PK that references the parent entity and a version column.i.e., something like this:create table Products (ProductID int identity primary key, ... columns that define the product which do not change from version to version ...)create table ProductVersions (ProductID int not null reference Products (ProductID), Version int not null, ... columns that define each version of the product ... primary key (ProductID, Version)) You should never really have an identity as part of a composite key. Chances are, these means that you should break things out into two tables..- Jeff |
|
|
|
takemyjazz
Starting Member
3 Posts |
Posted - 2006-12-01 : 15:14:59
|
quote:
Originally posted by jsmith8858YOu did say that you need two primary keys originally ....
I didn't mean to, off course :-)quote:
Sounds like you need two tables -- one table for your entity, with maybe a PK of an identity, and another table that contains versions of that entity, with a composite PK that references the parent entity and a version column.
I always knew that's the way it's supposed to be done but I was trying to avoid having two tables. Anyway, I think that's how I'm gonna do it.quote:
You should never really have an identity as part of a composite key. Chances are, these means that you should break things out into two tables..
I never said I wanted to have an identity as part of the composite key. I was just trying to explain that I wanted one of the fields to be "auto" incremented while the other one was treated manually.Thanks for your help, Jeff. |
|
|
|
|
|
|
|
|
Two primary keys with auto-increment