[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

SQL Server - Primary Key/Identity Value Difference

Posted on 2011-09-15
5
Medium Priority
?
280 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:seancurt
5 Comments
 
LVL 4

Expert Comment

by:rbride
ID: 36541763
Let me get this straight: you have declared a column with the INDENTITY property and when you insert to the table via the SP you read back the last inserted identity value through @@IDENTITY.

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.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36542384
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.

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

Open in new window

0
 

Author Comment

by:seancurt
ID: 36542799
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
0
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36543099
It looks like the Experts here already are helping you, but I will chime in only to say I would go with SCOPE_IDENTITY() over @@IDENTITY. You can try, SCOPE_IDENTITY() might work if the TRIGGER insert is treated as being in a different scope. http://msdn.microsoft.com/en-us/library/ms190315.aspx
0
 

Author Closing Comment

by:seancurt
ID: 36547940
Scope_Identity was the one, thanks !
0

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exchange administrators are always vigilant about Exchange crashes and disasters that are possible any time. It is quite essential to identify the symptoms of a possible Exchange issue and be prepared with a proper recovery plan. There are multiple…
This article explains how to move an Exchange 2013/2016 mailbox database and logs to a different drive.
In this Micro Video tutorial you will learn the basics about Database Availability Groups and How to configure one using a live Exchange Server Environment. The video tutorial explains the basics of the Exchange server Database Availability grou…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…
Suggested Courses

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question