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?
... 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:
marked_for_delete bit not null default 0
column feature.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.