primary keys

I am using the IDENTITY feature to generate the values fo rmy primary keys. I understand that primary keys need to be unique.

If I undestand correctly then they need to be uniquely identified in all table with in the sam database.

I am not sure if when I create a primary key using this feature. Do I need to change the seed and increment values in order to have primary keys for each table that are truely unique? This seems like the way to make all of the PK values in a database unique.

-Thanks to anyone that may be able to help

First off, a reminder that just setting an identity column doesn't make a row unique to the user. Having values:

Identity Name

1 Fred

2 Fred

Will make the two rows indistiguishable. So an Alternate Key on Name would make this far more ideal.

And yes, if you truly need your integer identity index keys to be unique in a database, a combination of seed and a check constraint will work:

create table first
(
firstId int identity(1,1) check (firstId between 1 and 100)
)
create table second
(
secondId int identity(101,1) check (secondId between 101 and 200)
)
go
insert into first default values;
insert into first default values;
insert into second default values;
insert into second default values;

select *

from first

select *

from second

firstId

--

1

2

secondId

--

101

102

Another method is to just use a guid for your keys. Of course guids are much larger and unwieldly to work with, but they are another way to go.

A big question is why do you need the values to be unique in the database? Just having them unique in the table they are in is generally good enough. It will definitely make it easier to work with since you won't have to be concerned with ranges of values later, especially if the data grows greater than initially expected.

|||

Primary key is the Domain Integrity Constraint. Here the Domain means a single table. Primary key insures the uniqueness of the table (single domain). It never validates other tables. Practically your understanding about PK is not possible. Bcs the number of records in each table is non-deterministic values. You can't make any assumption to accommodate this. Think about performance if i have more tables in my database.

But, UNIQUEIDENTIFIER typed values are used to identify the unique value across the table/database/server. Since it use the GUID on every generation the GUID must be unique.

|||

Thanks for the info. I need to do some studying on domain constraints and the different things that can be used to maintain integrity in this type of situation.

-Thanks

|||

Thanks for the info. I need to do some studying on domain constraints and the different things that can be used to maintain integrity in this type of situation.

-Thanks for your help