Grant Surridge
asked on
SQL Server - Primary Key/Identity Value Difference
Hi
I have a stored procedure that inserts some data into a table, it then returns @@Identity of the inserted record and updates another table with that value, and it links them together
However, I made the mistake of forgetting to make the field a primary key, and i wanted to put a foreign key on another table. So i have made it one. Now though, the @@Identity that is returned, does not match the value that is actually in the field - how can I get the 2 to correlate ?
I hope this makes sense !
Thanks
I have a stored procedure that inserts some data into a table, it then returns @@Identity of the inserted record and updates another table with that value, and it links them together
However, I made the mistake of forgetting to make the field a primary key, and i wanted to put a foreign key on another table. So i have made it one. Now though, the @@Identity that is returned, does not match the value that is actually in the field - how can I get the 2 to correlate ?
I hope this makes sense !
Thanks
Primary key does not accept duplicate value
Identity is the running value you can force fully change the start position of indentity and it will accept duplicates if you have changed starting point.
Identity is the running value you can force fully change the start position of indentity and it will accept duplicates if you have changed starting point.
create table #temptable (id int identity(1,1), varc varchar(10))
insert into #temptable (varc) values ('Alpesh')
insert into #temptable (varc) values ('Alpesh1')
insert into #temptable (varc) values ('Alpesh2')
Set Identity_insert #temptable ON
insert into #temptable (id,varc) values (2, 'Alpesh3')
Set Identity_insert #temptable OFF
insert into #temptable (varc) values ('Alpesh4')
select * from #temptable
ASKER
ahhhh. rbride you have made me think now !
I added a trigger to the database, that inserts to another table, so it will be the PK of that table that is being returned.
Hmmm. So now to get the ID of the other table
I added a trigger to the database, that inserts to another table, so it will be the PK of that table that is being returned.
Hmmm. So now to get the ID of the other table
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Scope_Identity was the one, thanks !
It is no problem to add primary / foreign key constraints afterwards: just use the appropriate ALTER TABLE command.
If you are using an IDENTITY field in the second table, that might be tripping you up because @@IDENTITY is the last inserted value on whatever table.
drop table t
go
drop table tfk
go
create table t (
id int PRIMARY KEY IDENTITY,
d varchar(10)
)
go
create table tfk (
id int,
t_id int FOREIGN KEY REFERENCES t (id),
d varchar(10)
)
go
DECLARE @ID INT
INSERT t (d) VALUES ('abc')
SELECT @ID = @@IDENTITY
SELECT @ID, * FROM t
INSERT tfk VALUES (1, @ID, 'def')
go
select * from tfk
go
Note: it is not really considered good practice to use the identity column as a primary key but for some circumstances it is pragmatic.