MySQL Relational Database Foreign Key

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 s开发者_StackOverflow社区ql 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?

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

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