-1

Searched, but more confused now then when I started. I have 2 tables, AlertsTable and AlertRecipientsTable.

I'd like to add a cascaded delete that says when an AlertRecipientID is deleted (the person was removed), I want it to automatically delete all records in the AlertsTable that have that removed AlertRecipientID.

AlertRecipientID is a field in both tables. I still want to maintain ability to create an alert recipient without necessarily having an alert record set up for that recipient.

I'm confused on how to set up this constraint in MySQL. Have confirmed all my tables are configured as InnoDB and am using MySQL version 5.6.36-cll-lve.

Example:

AlertsTable:

AlertID AlertType   AlertName   AlertUserID AlertRecipientID AlertDateTimeAdded
1       1           Test        1           56               2018-01-07 14:29:39

AlertRecipientsTable:

 AlertRecipientID   AlertRecipientUserID    AlertRecipientFirstName AlertRecipientLastName
 56                 1                       John                    Doe

When I delete John Doe with RecipientID 56, I'd like it to delete the entry in the AlertsTable named Test.

AAEM
  • 1,837
  • 2
  • 18
  • 26
Source Matters
  • 1,110
  • 2
  • 15
  • 35
  • Hi. What did you search? Did you search a version of "i'd like to ..." without your particular names? Google my comments re googling questions. Did you google for & read any intros to FKs and the manual re FKs? Because this is explained in just about any such presentation. It's odd that you actually used "cascade" in your title yet you say you couldn't find out how that works. – philipxy Jan 07 '18 at 22:16

1 Answers1

1

https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

ALTER TABLE AlertsTable
ADD FOREIGN KEY (AlertRecipientID)
REFERENCES AlertRecipientsTable (AlertRecipientID)
ON UPDATE CASCADE ON DELETE CASCADE;

This should work as you've described in your post. The foreign key constraint is on AlertsTable meaning no record can exist in that table with an AlertRecipientID that does not exist in AlertRecipientsTable, but you are free to have recipients in that table which do not have any entries within AlertsTable

abigperson
  • 5,252
  • 3
  • 22
  • 25