MySQL Relational Database Foreign Key
- by user623879
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?
