Sign in to follow this  
noatom

MySQL statement error

Recommended Posts

My experience with databases is very limited. 

The statement is:

"INSERT INTO eventCategoryMap(eventId, categoryId) VALUES (?, ?) ON DUPLICATE KEY UPDATE eventId = ?, categoryId = ?"

The question signs are  values which are added a bit later, before the command is executed. And it generates the following error:

ERROR: Failed inserting event-group mappings into the database. Cannot add or update a child row: a foreign key constraint fails (`databtest`.`eventcategorymap`, CONSTRAINT `fk_eventCategoryMap_category1` FOREIGN KEY (`categoryId`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION)

How should I modify the statement?

Edited by noatom

Share this post


Link to post
Share on other sites
Are you sure the data is correct?

The error indicates that the SQL engine understands the statement, but it cannot perform the action.

"foreign key" means that a value (the category, I think) references a key in another table.
Apparently you are violating a constraint here, perhaps the category you add does not exist in the other table as key?

(My SQL is very rusty too)

Share this post


Link to post
Share on other sites

so I just had a look at the database:

the table eventCategoryMap exists, and inside it there are the columns: id, eventId, categoryId

 

And the values which i actually input for that statement:

eventId =  2240001

categoryId = 0

 

 

 

So, I still don't really get what is wrong with that statement

Share this post


Link to post
Share on other sites

The question is, do you have a row with "categoryId = 0" already?

 

Since it's a foreign key, it is probably missing in another table.

 

 

 

Longer explanation:

Most (if not all) tables have rows, where one or more fields (together) must be unique, this is the key of that row, an unique identification.

Each row has such a key, and the key value of every row is different from every other row.

 

A row in one table can also refer to a row in another table. Such references are "foreign key". It allows linking data between different tables.

To reference to another table with a foreign key, the entry it points to must of course exist, or you're pointing to nowhere.

The data base can check such rules, and prevent you from making mistakes. In general, it will prevent you from adding such references.

Also, it will refuse removal until all rows in other tables pointing to it have been removed.

 

 

 

 

Share this post


Link to post
Share on other sites

just had a look and yes, eventId and categoryId are foreign keys, they reference to other tables. 

 

eventId  to the event table and to the id column

categoryId to the category table and to the id column

 

So I checked and those tables & columns exist. Then, I did a query on the category table and well it only contains 2 rows. One has the id set to 28, the other has it at 36.

So, neither row has an id of 0 currently.

 

The same with the event table, all rows have ids with different values other than 0.

 

 

 

However, I must ask: if a column has NULLABLE set to false, does that mean that 0 is not accepted as an input at all?

Edited by noatom

Share this post


Link to post
Share on other sites


eventId = 2240001
categoryId = 0
If "eventId" is one foreign key, and "categoryId" is another foreign key, then to insert this row into the event-category table, the event table must have a row with id 2240001, and the category table must have a row with id 0.

 

The data base checks both, but it started with the category-id for some reason, and it failed already, so it never checked the eventId until now.

 

NULLABLE = true  means the entry may be empty (or "unknown").

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this