Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2011
    Posts
    6

    Unanswered: using an FK and constraint together - best practice?

    I'm working on a datamodel where a certain sequence of approvals is required by certain approver types.

    Approver types are really set up in my system as roles. For example, there is a role table with an IsApprover flag:

    roleid rolename IsApprover
    1 Local Manager 1
    2 Region Manager 1
    3 User Admin 0

    So I have an ApproverSequence table with an ApproverRoleId FK which joins to the Role table.

    However, I don't want User Admin to be a valid approver selection so I could add a check constraint to the ApproverSequence table to enforce this.

    So my question is - If I add a check constraint to limit the role selection to a subset of roles then is it even worth it to have a FK on the ApproverSequence table to the Role table?

    It seems like the referential integrity would be enforced by the check constraint so the FK would be needless overhead.

    Is this a correct assessment?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    However, I don't want User Admin to be a valid approver selection so I could add a check constraint to the ApproverSequence table to enforce this.
    How are you planning to enforce that in the ApproverSequence table? Show us your actual SQL code.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •