MySQL: How can I insert values into a table with a foreign key?
Hey,
I'm currently creating a database in Notepad++. I've created four tables: Students, Books, Media, and Borrowing. I was able to enter values into Students and Books without any problems. However, I'm having trouble with Media and Borrowing because they reference Students and/or Books with a foreign key. Here's a small excerpt:
CREATE TABLE Buch ( BuchID INTEGER NOT NULL, Titel VARCHAR(255), Autorname VARCHAR(255), Erscheinungsjahr VARCHAR(255), Verlag VARCHAR(255), Sprache VARCHAR(255), PRIMARY KEY (BuchID)and
CREATE TABLE Medien ( MedienID INTEGER NOT NULL, Name VARCHAR(255), Typ VARCHAR(255), BuchID INTEGER NOT NULL, PRIMARY KEY (MedienID), FOREIGN KEY (BuchID) REFERENCES Buch(BuchID)If I now:
INSERT INTO Medien (MedienID, Name, Typ) VALUES (100, 'Die Kunst des Philosophierens', 'Buch');I get the error message:
"Cannot add or update a child row: a foreign key constraint fails" in MySql.
How can I improve this?
I am grateful for any help!
(1 votes)
Loading...
Why do you make life unnecessary hard for yourself with “shoulders”? The database does not matter to gender, for the user of the DB application the table or field names play no role, and for you it is unnecessary writing stuff, which also ensures that you have to comply with the exact spelling when used. The quark is not voluntary, but at most under compulsion.
Fine.
More helpful now would be what the table media should contain in detail. Because purely with the tables, it is not clear what the media should represent and why a medium should be linked to a book.
Purely from my interpretation, I would rather link a book to a medium – that is, the opposite direction, which does not make any sense with the titles in the medium. Works on me as if two tables were not really properly normalized here.
On the actual question:
Common error -> Data type of the fields involved is not identical.
Here, however, you set the Foreign Key to the BookID field, which contains “NOT NULL” as a condition.
Your INSERT does not bring value to BuchID….
100 -> Media. Media ID
“The Art…” -> Media. Name
“Book” -> Media.. Type
NULL -> Media. BookID <= here an integer is expected!
BTW: Name is already an appallingly bad choice for variables, tables or table fields, since “name” is often a keyword and can be interpreted quickly incorrectly!
Thank you. The names have been given to me, otherwise I would not normally do.
If the field names can quote, that’s no problem anymore.
For the IDs, you should consider setting AUTOINCREMENT.