September 17th, 2012, 02:49 AM
Entity relationships based on a column value
I have a table leads that takes on information from different sources (import, manual insert, post).
I have another table I called dupes which should keep track of the dupes that are removed from table leads.
I made a foreign key relationship between them and a on delete constraint. The problem is that data from leads table sometimes is removed for other reasons then because it was a duplicate. So I added a new column to leads that asks dupes?. If yes then its set to true and deleted, if not because its not a dupe my php sets it to false and it is deleted. So I want a relationship between leads and dupes tables to be setup where dupes table takes on only rows with true in dupes? column with a on delete constraint.
What kind of database relationship do I need to have to allow dupes table to take on only rows that are deleted from leads table because they were dupes.