resetting Identity Seed on change of primary key

I have a table that has a Primary key and a foreign key. The primary key is NOT an Identity field, however, the foreign key is. I would like to know if there is a way to have the foreign key reset itself to the value of 1 when the Primary key changes. For example if I add the following 3 records to the table: 1st record - Primary key is 1, foreign key is 1; 2nd record - Primary key is 1, foreign key is 2; third record - Primary key is 2, foreign key is 3, but I want the foreign key to be reset to 1.

Quote:

Originally Posted by Rick Kay

I have a table that has a Primary key and a foreign key. The primary key is NOT an Identity field, however, the foreign key is. I would like to know if there is a way to have the foreign key reset itself to the value of 1 when the Primary key changes. For example if I add the following 3 records to the table: 1st record - Primary key is 1, foreign key is 1; 2nd record - Primary key is 1, foreign key is 2; third record - Primary key is 2, foreign key is 3, but I want the foreign key to be reset to 1.

You should read the topic
DBCC CHECKIDENT
in books on-line help. If I understand correctly what you are trying, it won't work.

You will have to write code to generate your own FK values.

Tom.|||

Quote:

Originally Posted by folderol

You should read the topic
DBCC CHECKIDENT
in books on-line help. If I understand correctly what you are trying, it won't work.

You will have to write code to generate your own FK values.

Tom.

Tom, that's exactly what I thought, but I wanted to be sure someone else agreed with me. Thanks for your response.|||This will reseed the identity no for a column in a table.

declare @.intCounter int
set @.intCounter = 0
update (YOUR_TABLE)
SET @.intCounter = (YOUR_COLUMN) = @.intCounter + 1