I have table as below:
+----------+---------+
| ParentId | ChildId |
+----------+---------+
It is a one-to-many relationship (one parent, multiple children) at the moment the primary key is the child id because a child can only map to one parent (all pretty straight forward so far).
My question comes in now tho I want to add a secondary column as below:
+----------+---------+---------+
| ParentId | ChildId | Deleted |
+----------+---------+---------+
So the situation is I need to keep track, for audit reasons, mappings of deleted parents, my thought was to make Child Id unique iff Deleted is well false. Question is how do I achieve this? Is it possible or should a create a secondary table that I used to archive the deleted entries, any other suggest how I can either achieve this or another approach I can take?
I think this is your question:
my thought was to make Child Id unique iff Deleted is well false. Question is how do I achieve this?
If so, you can use a filtered unique index:
create unique index unq_t_parent_child on t(parent, child)
where isdeleted = 0;