I have a foreign key between two columns of the two of my tables. Problem is, I also have a "Deleted" column and I consider the record as deleted if it's set to true from my UI. So my foreign key must check that column is set to true or false as well.
Is there any way to do this? I need to create rule like: "Don't allow the second table's Deleted column to set to false, if there are any related records on the first table."
Here is a long explanation if the above is little too complicated:
You could use a trigger for that:
create trigger trg_upd_status before update on StatusType for each row begin declare customer_count int; if new.Deleted = 'true' then select count(*) into customer_count from Customer where Deleted = 'false'; if (customer_count > 0) then signal sqlstate '45001' set message_text = "Not allowed to delete this record"; end if; end if; end; /
This assumes the data type of the Deleted column is
varchar, but you could adapt it easily if it is numerical or a bit...
Note that this only checks whether there are
Customer records that are not deleted. So this means you also have to do the opposite: if you un-delete a
Customer record, the corresponding
StatusType record should not be in deleted status. Same if you update the
Customer record and change the
StatusId. That would be a trigger on the