1

Suppose I have a table called Actor:

Actor_ID (pk) (AI), Actor_Name          // pk = primary key, AI = auto inc

Then I have a table called Character:

Character_ID (pk) (AI), Actor_ID (fk), Character_Name

Now I want to add a new Character to my Database.

  • I check to see if the actor for this character already exists in the Actor table
  • If it does, I get the Actor_ID
  • If it doesn't, I add the actor to the Actor table, and get the Actor_ID
  • I add the new character to the Character table using the Actor_ID

My problem:

When I insert a new actor, do I have to open a new connection to the database. Insert the actor. Then close the connection. Then open a new connection to insert the character.

The reason I ask this is because I'm getting the error:

Cannot add or update a child row: a foreign key constraint fails

when I try to add the character. I'm guessing it must be because the Actor_ID isn't in the Actor table yet.

Could someone please clarify this for me? Is there an easier way to do this than opening many connections?

Greg Peckory
  • 7,700
  • 21
  • 67
  • 114
  • Why do you need to open many connections? What's the problem with just one connection? – Dekel Oct 16 '16 at 02:51
  • See **Section2 / What does Show your Schema Mean?** of [What is Sqlfiddle and why should I care?](http://stackoverflow.com/a/38899465) to help us to help you. If you do that for the 2 or 3 (whatever) tables involved, we can clearly show you the data error – Drew Oct 16 '16 at 02:52

0 Answers0