2

Given that I have a table structure where I have a parent that can have "Roles" and that parent have children that can have the same "Roles", how can I create a constraint on the child table that verifies that the parent does not have the Role that is about to be inserted?

Like this:

Parent
 |
 \- ParentRoles
 |
 \- Child
    |
    \- ChildRoles

I want the union of the Child and Parent roles to be a list of unique roles (no duplicates).

I have checked Constraint by UserFunction (not recommended but would work) and tried creating a view with an unique index but ran into trouble because I have to use Union in the view and then I cannot index it.

An answer on the Constraint by UserFunction question suggests using Foreign Keys but I do not understand how I could make that work given my table structure.

Community
  • 1
  • 1
Kristoffer L
  • 728
  • 1
  • 9
  • 20

1 Answers1

0

how can I create a constraint on the child table that verifies that the parent does not have the Role that is about to be inserted?

You can not - constraints can not easily o that. I would go with a trigger.

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • Constraint does not absolutely have to mean the SQL Server Constraint, it could be any form of database functionality. I just want the database to ensure that inserted data is correct. – Kristoffer L Nov 01 '12 at 09:42
  • Well, you know the word "context"? "Limit data in sql server in a constraint" means quite brutally a sql server constraint. Anyhow, just use a trigger. – TomTom Nov 01 '12 at 11:08