# MySQL statement error

This topic is 1026 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

## 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 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 on other sites

a foreign key constraint fails ... FOREIGN KEY (categoryId) REFERENCES category (id)

This is important. You are attempting to add an eventCategoryMap to a non-existent category. Check that a category exists for your categoryId.

##### 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 on other sites

categoryId = 0

There is no entry in the 'category' table with 0 as its id.

I don't know if 0 is even a valid id in this case

##### 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 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 on other sites
No. NULL is very different from 0. NULL basically means the value of this field is not known, whereas 0 is a known numerical value.

##### 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").

1. 1
Rutin
23
2. 2
3. 3
JoeJ
20
4. 4
5. 5

• 29
• 40
• 23
• 13
• 13
• ### Forum Statistics

• Total Topics
631740
• Total Posts
3001959
×