0

I have a table with 2 foreign keys that reference the same field within another table. I know how to define the foreign key constraint to remove my table entry if at least one of the two foreign keys become deleted. But instead I want to keep the table entry if at least one of the foreign keys still exist?

CREATE TABLE PrivateMessages
  ...
  INDEX(FromEmail, ToEmail),
  FOREIGN KEY(FromEmail, ToEmail) 
    REFERENCES Users(Email, Email) 
    ON UPDATE CASCADE 
    ON DELETE CASCADE,
  ...

The table stores messages between two users. I want to delete messages if both users don't exist any longer, only. Maybe, is there a better approach to realize this?

Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82
PAX
  • 1,056
  • 15
  • 33

1 Answers1

1

... I want to keep the table entry if at least one of the foreign keys still exist

I want to delete messages if both users don't exist any longer, only.

It can't be achieved just by defining a constraint.

Possible procedure:

  1. Define a marked_for_delete bit not null default 0 column feature.
  2. Whenever you want to delete a user, mark it for deletion, by an update call.
  3. Define either a before or an after type of delete trigger on the table.
  4. In the trigger body, check if the combo of fromEmail and toEmail are marked for deletion, then execute a delete statement on the parent table and the child combo will be deleted as your required condition matched.
Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82