12

How to insert records in one to one relationship?

lets say i have 3 tables : table A , table B, table C

I have to insert a record into those tables which table A is referenced as the main table for primary key.

Lets just say like this:

tableB.PK = tableA.PK
tableC.PK = tableA.PK

Now,

when I am inserting records in tableB or tableC the error occurs:

Error Message: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tableB_tableA"

But when I am inserting in tableA which is the basis of the two tables' primary keys, it is ok and it increments.

How would I insert a record? Specifically in JPA.

  1. Do I need to insert into tableA first then insert in others? (Just did that and it does not work)
  2. Do I need to insert data in the tables at the same time? (How? in JPA will be good)
bpunzalan
  • 378
  • 1
  • 6
  • 24
  • 3
    since you have a reference in tableB and tableC to tableA, you have to insert tableA first and retreive the generated PK. Then you can insert in any of the other tables with the retrieved ID. For JPA have a look here: http://stackoverflow.com/questions/9649515/how-to-get-id-of-last-persisted-entity-using-jpa – deterministicFail May 23 '14 at 07:16
  • @deterministicFail: this is more of an answer rather than a comment – Brett Schneider May 23 '14 at 07:25
  • Oh ok I get it, I need to retrieved the generated PK to make a valid insertion. the link helps though. thanks. But why is that I'm trying to insert directly in database and i get the error while inserting to tableB after I insert in tableA although I'm placing the ID number in the tableB which is present in tableA? – bpunzalan May 23 '14 at 07:27
  • without your source i can just assume you are working with a transaction and try to insert in a different session context. Or you simple use the wrong id – deterministicFail May 23 '14 at 07:32
  • I get it in JPA it must be within transaction. but how about in sql server. Just curious. Im entering 1 manually in tableB.PK wherein tableA.PK has 1. And it occurs error I can't enter a record in tableB even I'm typing the right ID. – bpunzalan May 23 '14 at 07:37
  • Is IDENTITY used for Tables B and C? And is IDENTITY_INSERT ON or OFF? http://msdn.microsoft.com/en-us/library/ms188059.aspx – NickyvV May 23 '14 at 07:55

1 Answers1

13

Here again my comment as an answer

since you have a reference in tableB and tableC to tableA, you have to insert tableA first and retreive the generated PK. Then you can insert in any of the other tables with the retrieved ID. For JPA have a look here: How to get Id of last persisted entity using JPA

And here is an example how to achieve this in sql server: http://sqlfiddle.com/#!3/a3f62/3

this is a basic in relational databases and foreign keys, i didnt read the wiki article, but it have to mention same the thinkg: http://en.wikipedia.org/wiki/Foreign_key

Community
  • 1
  • 1
deterministicFail
  • 1,271
  • 9
  • 28