Search code examples
mysqlsqlmysql-error-1452

SQL Error 1452 while UPDATE non key columns

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...

Solution

  • 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.)