How to construct table with unique PK

Let say I have 6 tables. I want to autogenerate the PK for each table and that is unique for each table and cant be duplicated on other tables. Let say I have table with PK of 1, so table2 to table6 wouldnt have a PK of 1. If table2 have a PK of 2, table1, table3 to table6 wouldnt have a PK of 2. Same for others. Identity will not be appropriate. Will 'uniqueidentifier' data type suffice? How bout guid? Or what must be my datatype? Or what will I do to implement this? Any links? Thanks

What you describe isn't really applicable to a 'normal' primary key.
In the 'normal' case, a PK doesn't care about the values of other PK's in other tables.

There are cases where one would want to distribute PK values like you describe, though I don't know if that's your reason.
(eg some replication / distribution strategies)

However, if you want to read some about keys, here's a link that mayshed some light.
http://www.datamodel.org/DataModelKeys.html

=;o)

/Kenneth

|||

have a composite primary key

key1 and key2

have key1 to have a default value of "A" for the first table, "b" for the second and "c" for the third

key2 would be an identity

your pk would be a combination of key1 and key2

and that would definitely be unique across the enterprise

|||

hi KeWin,

its implemented in a CRM thats why we're try to do the same. Any ideas how?

joeydj,

How would I get the latest PK, for example A3456 is the most recent. So the next generated PK is A3457. Can you show a sprocs that would do that. Thanks. :)

|||

Well, personally I'm not too keen on keys like 'A3457' - where 'A' has some significant meaning.
This is what's called an 'intelligent key' or 'concatenated key' (because the key itself is made up of several parts that has independent meaning), and is ususally something you want to avoid.

What a certain key should look like - well, it depends.
It's a desginer's choice, and it's part of the datamodel.

There may be a number of reasons behind any PK's design, the most important thing about it (imo) is that the designer really understands what a primary key is, and why he/she want it implemented in a certain way. Be it natural or a surrogate, it should be deliberate with some thought behind it.

This leads to the next question, how to decide?
There's no simple answer to that question, unfortunately, it's one of those 'it depends' things....

Your best tool is understanding, both of the database in question, the business it should support, and the concept of primary keys etc.
(I don't have any good links handy, but google usually manages to come up with something =;o)

/Kenneth

|||

portect wrote:

hi KeWin,

its implemented in a CRM thats why we're try to do the same. Any ideas how?

joeydj,

How would I get the latest PK, for example A3456 is the most recent. So the next generated PK is A3457. Can you show a sprocs that would do that. Thanks. :)

try this. this is called a composite pk solution. the advantage is that you dont need to worry whats

the next number in sequence. the problem is joining with other table.

CREATE TABLE [mytable] (
[key1] [char] (1) NOT NULL CONSTRAINT [DF_mytable_key1] DEFAULT ('A'),
[key2] [int] IDENTITY (1, 1) NOT NULL ,
[data1] [char] (10) NULL ,
[data2] [char] (10) NULL ,
CONSTRAINT [PK_mytable] PRIMARY KEY CLUSTERED
(
[key1],
[key2]
) ON [PRIMARY]
) ON [PRIMARY]
GO

insert into mytable(data1,data2)values('mydata1a','mydata2a')
insert into mytable(data1,data2)values('mydata1b','mydata2b')
select * from mytable

|||

an alternative solution here.

remember that an identity has a seed and increment parameter.

seed Is the value that is used for the very first row loaded into the table.

this is called an identity seed and contraint solution.

first table shall be between 1 and 10M

pk type bigint, identity on, seed=1

data type bigint

constraint: pk between 1 and 10,000,000

second table shall be between 10M+1 and 20M

pk type bigint, identity on, seed=10,000,0001

data type bigint

constraint: pk between 10,000,001 and 20,000,000

third table shall be between 20M+1 and

pk type bigint, identity on, seed=20,000,0001

data type bigint

constraint: pk between 20,000,001 and 30,000,000

if your not happy with 10M difference make it 100M.

this solution seems better to me.

|||Thanks all. I think surrogate keys will be appropriate.|||

follow up question:

http://forums.microsoft.com/MSDN/showpost.aspx?postid=857019&siteid=1

thanks.

sql