Error code: 1451 with 2 primary keys

Question

I cannot run the following statement:

delete from UT_Session where my_id='5146' and upgrade_time='2016-01-03 17:25:18'

Since I get:

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`Upgrade_tool`.`pre_tasks`, CONSTRAINT `pre_tasks_ibfk_1` FOREIGN KEY (`my_id`) REFERENCES `UT_Session` (`my_id`))

There are the tables:

create table UT_Session
(
my_id int not null,
upgrade_time DATETIME not null,
ownerName varchar(20),
source varchar(20) not null,
target varchar(20) not null,
isClosed boolean,
primary key (my_id,upgrade_time)
)ENGINE=INNODB;

CREATE INDEX upgrate_time_index
ON UT_Session (upgrade_time);

create table pre_tasks
(
    my_id int,
    foreign key (my_id) references UT_Session(my_id),
    upgrade_time DATETIME not null,
    foreign key (upgrade_time) references UT_Session(upgrade_time),
    pre_task_type varchar (100),
    reason varchar(500),
    primary key (my_id,pre_task_type,upgrade_time)
)ENGINE=INNODB;


create table post_tasks
(
    my_id int,
    foreign key (my_id) references UT_Session(my_id),
    upgrade_time DATETIME not null,
    foreign key (upgrade_time) references UT_Session(upgrade_time),
    post_task_type varchar (100),
    reason varchar(500),
    primary key (my_id,post_task_type,upgrade_time)
)ENGINE=INNODB;

Now when I run this querys to show the content of the tables, I get this:

mysql> select * from UT_Session where my_id='5146';
+------+---------------------+-----------+----------+----------+----------+
| my_id | upgrade_time        | ownerName | source   | target   | isClosed |
+------+---------------------+-----------+----------+----------+----------+
| 5146 | 2016-01-03 17:25:18 | Ronen     | 5.1.2.12 | 5.2.2.26 |        0 |
| 5146 | 2016-01-03 17:35:10 | Ronen     | 5.1.2.12 | 5.2.2.26 |        0 |
| 5146 | 2016-01-03 17:36:57 | Ronen     | 5.1.2.12 | 5.2.2.26 |        1 |
+------+---------------------+-----------+----------+----------+----------+

mysql> select * from pre_tasks  where my_id='5146';
+------+---------------------+--------------------------------------------------------+--------+
| my_id | upgrade_time        | pre_task_type                                         | reason |
+------+---------------------+--------------------------------------------------------+--------+
| 5146 | 2016-01-03 17:36:57 | Type 87954r0f                                                                          |        |
| 5146 | 2016-01-03 17:36:57 | Type 1a79F4rf                                                                          |        |
+------+---------------------+--------------------------------------------------------+--------+

mysql> select * from post_tasks  where my_id='5146';
+------+---------------------+--------------------------------------------------------+--------+
| my_id | upgrade_time        | post_task_type                                        | reason |
+------+---------------------+--------------------------------------------------------+--------+
| 5146 | 2016-01-03 17:36:57 | Type v7d54r8f                                                                          |        |
+------+---------------------+--------------------------------------------------------+--------+

As you can see both the "my_id" and the "upgrade_time" are keys, and the row I want to delete is not present on the child tables, because it's a different time.

What's wrong here?

Thanks!

Rahul · Accepted Answer · 2016-01-05T08:45:41.343

1

Yes but you do have records in child or referencing table with my_id='5146' and so it's throwing that error correctly. If you really want to delete that record then delete them first from child tables and then from parent.

You could have created your FOREIGN KEY constraint with ON DELETE CASCADE cascading option saying below in which the delete would have been cascaded to the child tables as well

foreign key (my_id) references UT_Session(my_id) on delete cascade

What you are actually trying to do is create a composite foreign key like below; in which case uniqueness of the records will depend on both the key.

FOREIGN KEY (my_id, upgrade_time)  
  REFERENCES UT_Session (my_id, upgrade_time) ON DELETE CASCADE

edited Jan 05 '16 at 08:45

answered Jan 05 '16 at 07:57

Rahul

76,197
13
71
125

But that's the point of having 2 primary keys, isn't it? That those are not childs because one of the keys if different. – Yosi Tagger Jan 05 '16 at 08:34
1

No, you are getting confused. Irrespective of how many pk's you have if you have a matching key in child table; FK constrained validation will fail resulting in what you see. – Rahul Jan 05 '16 at 08:36
What you want to do is declare your FK as composite FK in which case it shouldn't fail. – Rahul Jan 05 '16 at 08:38
Then if I use the ON DELETE CASCADE, whenever I delete from a child let's say 'my_id'=5146 and time='12:00' it will delete from the parent all the rows with id=5146 regarding of time!! – Yosi Tagger Jan 05 '16 at 08:38
Isn't this a composite key? primary key (my_id,upgrade_time) – Yosi Tagger Jan 05 '16 at 08:44
NO, I mean composite foreign key. See edit in answer if that helps. – Rahul Jan 05 '16 at 08:46
Like this create table pre_tasks ( my_id int, upgrade_time DATETIME not null, pre_task_type varchar (100), reason varchar(500), primary key (my_id,pre_task_type,upgrade_time) FOREIGN KEY (my_id, upgrade_time) REFERENCES UT_Session (my_id, upgrade_time) ON DELETE CASCADE )ENGINE=INNODB; – Yosi Tagger Jan 05 '16 at 09:35
1

Yes, exactly that's what I said. – Rahul Jan 05 '16 at 09:41
Thanks!! that did it. – Yosi Tagger Jan 05 '16 at 11:06

Error code: 1451 with 2 primary keys

1 Answers1