0

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?

Heinrich
  • 2,144
  • 3
  • 23
  • 39

1 Answers1

1

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I have had a look into these types of index's my question around that is what is the performance hit on using these, note I am not expecting to many writes but possibly a lot of reads. – Heinrich Jun 04 '17 at 23:15
  • @Heinrich . . . This is how you have the database enforce the constraint you are asking for. There is some overhead for such an index, as there is with any other index. It is probably the cheapest way to implement such a constraint. – Gordon Linoff Jun 04 '17 at 23:20