How do I delete row with primary key using foreign key from other table?

Question

I have a table called 'agenda' //translation: dairy

with the following rows:

idagenda // primary key
title
waar
organisatie
...
etc.
...

I also have a table for the date of an diary item/event called agendadatum

with the following rows:

id // primary key
idagenda // id from other table
van //from
tot // till
datum // date

When the field 'tot' is the date from Today it will delete the rows from the database, but the rows in the 'agenda' table remain untouched. They're not deleted, because I did not call them.

My delete query looks like this:

DELETE FROM agendadatum WHERE tot < NOW();

How can I also delete the rows from 'agenda' table, that have the same id then the foreign key in agendadatum?

I understand you want to delete a whole agenda when it contains *any* entry whose `tot` date is past, am I right? — RandomSeed, Aug 22 '13 at 14:14
currently, I am deleting only the row from agendadatum where the field `tot` = NOW(); as you can see, this table has an idagenda from the other table. I use joins to show them on 1 page. I don't know how to delete both rows. not only from agendadatum but from agenda too. So I only want to delete the row that has the same id then the foreign key from agendadatum. — Kees Sonnema, Aug 22 '13 at 14:17
But this will also delete `agenda`'s having several `agendadatum` entries, and possibly some with a `tot` date in the future. Are you okay with this? — RandomSeed, Aug 22 '13 at 14:20
No it won't. my table is called agenda, it has field like id, title, organisation etc. the id from that table is the same as the `idagenda` in my agendadatum table. If I delete the agendadatum row, I would like to delete the one with the idagenda the same as the one from agendadatum to be deleted also. — Kees Sonnema, Aug 22 '13 at 14:22
Imagine you have an Agenda with (agendaid = 1), and two Agendadatum like (id = 1, agendaid = 1, tot = '2012') and (id = 2, agendaid = 1, tot = '2014'). The first agendadatum matches your criteria, then Agenda [1] should be deleted, leaving agendadatum [2] orphaned. — RandomSeed, Aug 22 '13 at 14:25
The agendadatum is the second table, so If I insert into agenda and add datums they always ahve the insert_id from the agenda table :) — Kees Sonnema, Aug 22 '13 at 14:27
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/36022/discussion-between-randomseed-and-kees-sonnema) — RandomSeed, Aug 22 '13 at 14:28

score 0 · Answer 1 · edited May 23 '17 at 12:29

0

In your create table you need to mention the Foreign Key Constraint like

FOREIGN KEY (product_id) REFERENCES products (id)
       ON DELETE CASCADE
       ON UPDATE CASCADE,

and after that If you run the delete query, automatically the rows will be deleted, which is referencing the ids of the table

You can go through the explanation present in delete on cascade

edited May 23 '17 at 12:29

Community

1
1

answered Aug 22 '13 at 13:16

Sashi Kant

13,277
9
44
71

How can I do this in phpmyadmin relation view? and could you also use my units? – Kees Sonnema Aug 22 '13 at 13:17
I have this, but it's giving an error: `FOREIGN KEY (idagenda) REFERENCES agenda (idagenda) ON DELETE CASCADE, ON UPDATE CASCADE, DELETE FROM agendadatum WHERE tot < NOW();` – Kees Sonnema Aug 22 '13 at 13:30
1

@KeesSonnema This was an example of how you would define the foreign key on `agenda`. Either recreate your table or modify it it with [`ALTER TABLE`](http://dev.mysql.com/doc/refman/5.6/en/alter-table.html). – RandomSeed Aug 22 '13 at 14:12
could you give an alter table example, because I can't find that anywhere. – Kees Sonnema Aug 22 '13 at 14:13

score 0 · Answer 2 · answered Aug 22 '13 at 13:27

if you are using php you can try the following

//get all agentadatum data with date now and before
$select_agendadatum = mysql_query("SELECT * FROM agendadatum WHERE tot <= NOW()") or die (mysql_error());
//loop through rows
while($row_agendadatum = mysql_fetch_assoc($select_agendadatum))
{
     //delete agendadatum row
     $delete_agendadatum = mysql_query("DELETE FROM agendadatum WHERE id = '".$row_agendadatum['id']."'") or die (mysql_error());
    //delete agenda row
    $delete_agenda= mysql_query("DELETE FROM agenda WHERE idagenda = '".$row_agendadatum['idagenda']."'") or die (mysql_error());
}

if your logic accepts more than one agendadatum per agenta you can simply count if there is more than 1 agendadatum in a specific agenta before deleting the agenta...

hope this helps

I'm not going to use mysql_* queries anymore. they're deprecated. — Kees Sonnema, Aug 22 '13 at 13:28

score 0 · Accepted Answer · answered Aug 22 '13 at 14:23

0

You can delete from multiple tables in one query:

DELETE agenda.*, agendadatum.*
FROM agenda
JOIN agendadatum USING (idagenda)
WHERE tot < NOW();

This will delete rows from both agenda and agendadatum, and only those rows matching the conditions (same rows as returned by the query if you replaced DELETE with SELECT).

answered Aug 22 '13 at 14:23

RandomSeed

29,301
6
52
87

Wow didn't knew about `USING` will try this :D – Kees Sonnema Aug 22 '13 at 14:24
This is just a syntactic shortcut for `ON t1.field = t2.field` when both fields have the same name. Handy, nonetheless. – RandomSeed Aug 22 '13 at 14:26

How do I delete row with primary key using foreign key from other table?

3 Answers3

Linked