CODEWITHSUNDEEP
sql-server
Nitesh
Nitesh

Reputation: 11

Assigining primary key column value to foreign key column

I have two tables called VisitorMaster and SupportVisitor. In VisitorMaster I have a column called VisitorID1 which is a primary key (but not an identity column). In SupportVisitor I have a column called VisitorID2 which acts as a foreign key for the VisitorMaser table.But my requirement is I want the last generated column value of VisitorID1 to be inserted in VisitorID2 of SupportVisitor table. How to achieve this.. Please Help??

Upvotes: 0

Views: 80

Answers (2)

Steve Pettifer
Steve Pettifer

Reputation: 2043

I'm thinking your best bet would be to ensure that you wrap your INSERT into the VisitorMaster table with a transaction and that within the transaction you obtain the value of VisitorID using the data you've inserted to query it and finally putting that into SupportVisitor before either committing or rolling back the lot.

You could use a trigger to do this but triggers are nasty things that can have all kinds of unintended consequences since the operate more or less invisibly so I'd steer clear of them.

Ultimately though I'd ask why have you got a non-identity surrogate PK in one column? It's a pretty bad bit of design. Either use a natural key (combination of forename, surname, timestamp or whatever makes a natural unique key) or use an auto-incrementing identity field as a surrogate PK (whereupon you then can use SCOPE_IDENTITY) because otherwise your key is pretty shaky and not guaranteed to be unique. How are you generating this value? Is it held in a separate table (I've known some databases use this kind of system, especially EAV model databases, and it is not a good system in any way shape or form and isn't much easier to deal with than arbitrary values)? If you have any influence over this design at all then you should change it because this smells and is likely to cause you many, MANY more problems in future.

Upvotes: 1

Elken
Elken

Reputation: 182

After inserting a record into a table with an IDENTITY-column you can retrieve this rows numeric identity by selecting the scope identity:

INSERT INTO VisitorMaster (Name) VALUES ('Jane doe')

SELECT SCOPE_IDENTITY() -- Will retrieve ID for Jane Doe

Upvotes: 0

Related Questions