MySQL Relational Database Foreign Key

Question

To learn databasing, I am creating a movie database.

To associate multiple directors with a movie, I have the following schema:

movie(m_ID, ....) m_director(dirID, dirName)//dirID is a autoincrement primary key m_directs(dirID, m_ID) //dirID, m_ID are set as foreign Keys in the mysql database(InnoDB engine)

I have a program that connects to the db that needs to add a movie to the database.

I can easily add a new entry to the movie table and the m_director table, but I am having trouble adding a entry in the m_directs table.

INSERT INTO m_director (dirName) VALUES("Jason Reitman"); INSERT INTO m_directs (dirID, m_ID) VALUES(LAST_INSERT_ID(), "tt0467406");

I am using this sql statement to insert a new director and add the association to the movie. I know the primary key of the movie, but I don't know the dirID, so I use LAST_INSERT_ID() to get the last id of the director just inserted.

The problem I am having is that I get the following error:

MySql.Data.MySqlClient.MySqlException (0x80004005): Cannot add or update a child row: a foreign key constraint fails (`siteproducts`. `m_directs`, CONSTRAINT `m_directs_ibfk_2` FOREIGN KEY (`dirID`) REFERENCES `m_directs` (`dirID`) ON DELETE CASCADE ON UPDATE CASCADE)

Any ideas?

try selecting LAST_INSERT_ID() instead and make sure that it is the value you are expecting, also try inserting manually with a known good value and see which is incorrect. — Stefan H
– Stefan H, Commented Mar 6, 2011 at 8:02
I think you'll need to show us the output of show create table m_director and show create table m_directs. — Ted Hopp
– Ted Hopp, Commented Mar 6, 2011 at 8:05

rdamborsky · Accepted Answer · 2011-03-06 08:06:23Z

2

Looks like you have foreign key for m_directs.dirID set to same table and column, not to m_director.dirID

answered Mar 6, 2011 at 8:06

rdamborsky

1,9301 gold badge16 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user623879 Over a year ago

That was the prob, how'd you catch that lol...first time setting up a db is bound to have some minor mistakes..Thanks very much!

xkeshav · Accepted Answer · 2011-03-06 08:22:18Z

as u write LAST_INSERT_ID() , but your insert query on m_director is not executed, so last insert_id will come from movie table..

or as per @rdamborsky

you have foreign key for m_directs.dirID set to same table and column, not to m_director.dirID

Collectives™ on Stack Overflow

MySQL Relational Database Foreign Key

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related