SQL Error 1452 while UPDATE non key columns

Question

I have those two tables...

CREATE TABLE `Mail` ( `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `sender` varchar(255) NOT NULL DEFAULT '', `receiver` varchar(255) NOT NULL DEFAULT '', `text` longtext , PRIMARY KEY (`timestamp`,`sender`,`receiver`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

... and ...

CREATE TABLE `MailHeader` ( `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `sender` varchar(255) NOT NULL DEFAULT '', `receiver` varchar(255) NOT NULL DEFAULT '', `title` varchar(45) DEFAULT NULL,, `seen` int(11) DEFAULT '0', `reply` int(11) DEFAULT '0', PRIMARY KEY (`timestamp`, `sender`, `receiver`), CONSTRAINT `MailHeader_ibfk_1` FOREIGN KEY ( `timestamp`, `sender`, `receiver`) REFERENCES `Mail` (`timestamp`, `sender`, `receiver`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When I try to UPDATE a non key column like that way:

UPDATE MailHeader SET `title` = ?, `seen` = ?, `reply` = ? WHERE `sender` = ? and `receiver` = ?;

Than I always get that error:

com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException:

Cannot add or update a child row: a foreign key constraint fails (usr_web4930_1.MailHeader, CONSTRAINT MailHeader_ibfk_1 FOREIGN KEY (timestamp, sender, receiver) REFERENCES Mail (timestamp, sender, receiver)

I tried the most trivial way, with one record in both tables and used the "MySQL-Workbench" tool to change a non-key column. With exactly the same error. I realy don't get it...

Please post sample data and an UPDATE query which will reproduce the issue. — Paul Spiegel
– Paul Spiegel, Commented Jun 10, 2018 at 19:47
Please also replace ... with actual code. If you have ON UPDATE CURRENT_TIMESTAMP for the timestamp column - that would be the issue. — Paul Spiegel
– Paul Spiegel, Commented Jun 10, 2018 at 19:50
You are realy fast, guys. :) After reading "ON UPDATE CURRENT_TIMESTAMP" everything got clear. Thanks! ;) — Invitro Guy
– Invitro Guy, Commented Jun 10, 2018 at 20:00

Paul Spiegel · Accepted Answer · 2018-06-10 20:31:25Z

As you have found out, you are not only updating non key columns. The ON UPDATE CURRENT_TIMESTAMP attribute will also update the timestamp column, which is part of the foreign key.

Removing that attribute will solve the actual issue. But you should also do more changes:

Remove DEFAULT CURRENT_TIMESTAMP from the MailHeader table, since you will always want to insert the correct timestamp from the parent table.

Remove ON UPDATE CURRENT_TIMESTAMP from the Mail table, to avoid the same problem if you ever want to update a row. If you never update, then you also don't need that attribute.

Farther I'd suggest to use an AUTO_INCREMENT PRIMARY KEY.

It's also not clear why you need the MailHeader table at all. You could just as well add the columns title, seen and reply to the Mail table. (I guess a mail cannot exist without a header.)

Hello again and sorry for the late answer. I changed those definitions like you mentioned. Thanks to far. Well the MailHeader indicates an active conversation between two people. If you got a MailHeader entry, then there must be at least one Mail it points to (foreign key to parent table). I use this as an index-table metaphor to fetch active conversations fast. The conversation is represented by the Mail records. So there could be one or more Mails. The header is used as an pointer to the last mail record.
In runtime, the user is getting an overview by querying of the appropriate MailHeader. Now the user is choosing one entry, and the matching Mail records will be shown as an "chat like" look. Btw.: cannot upvote your post, because of missing reputations.

Collectives™ on Stack Overflow

SQL Error 1452 while UPDATE non key columns

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related