CODEWITHSUNDEEP
mysqlmysql-workbench
Limnic
Limnic

Reputation: 1876

MySQL how to cascade on delete without foreign key

Alright so first let me introduce my schema.

table: users

Field    |     Type    |  Key  |  NN   | AI
---------------------------------------------
id       | int(11)     | PRIM  |  YES  | YES
username | varchar(50) |       |  YES   | NO
banned   | int(1)      |       |  YES   | NO

Foreign keys: none

table: macs

Field    |     Type    |  Key  |  NN   | AI
---------------------------------------------
id       | int(11)     | PRIM  |  YES  | YES
address  | varchar(50) |       |  YES   | NO
lastacc  | timestamp   |       |  YES   | NO

Foreign keys: none

table: usermac

Field    |     Type    |  Key  |  NN   | AI
---------------------------------------------
userId   | int(11)     | PRIM  |  YES  | NO
macId    | int(11)     |       |  YES  | NO


Foreign keys:
[FK: macId] references macId to id in table macs
[FK: userId] references userId to id in table users

The scenario is, I have a userId and wish to delete all records in macs that are linked to that userId (as stated by records in usermac)

The relation between users and macs: a user can have multiple macs and a mac can belong to multiple users.

What SQL query should I perform for this? I tried cascading delete but cascading does not work.

Thanks in advance for your answer.

Upvotes: 2

Views: 1621

Answers (2)

Pankaj Sharma
Pankaj Sharma

Reputation: 679

DELETE FROM mac WHERE id in (SELECT macId from usermac WHERE userId = <userId of deleted User>);

Hope this helps.

Upvotes: 2

LHristov
LHristov

Reputation: 1123

The simpler the better:

DELETE FROM usermac WHERE userId=1;

will delete all macs for user 1

Upvotes: 0

Related Questions