-- Enforce Integrity ---- either this table or this other one SQL Server 2005

Posted on 2007-10-17
Last Modified: 2010-03-19
-- Enforce Integrity ----

I have probably structured this wrong so any advice critical or not is fine :)
I have an application whichs tracks assets (computers)

In my parts_master table I record the current_location and would like to force integrity with two master location tables

eg the asset can either within our own branch network or in a 3rd parties branch network and therefor have two tables holding the branches details (structured different as need to hold different data)  How can I enforce that the current_location in parts_master is in either one of our brances or one of the thirdparty branches.  Sorry thats really confusing....

InternalSites (PK BranchID)
ExternalSites(PK BranchID)
Parts_Master (BranchID) enforce from either table above - the ranges of branch numers between tables is easily distinguisable.

I'm sure there's a better way to structure this.

Thanks for your time

Question by:skiltz
    LVL 22

    Expert Comment

    I know of no way to accomplish this with a Foreign Key constraint. However, you could accomplish this with a BEFORE INSERT and BEFORE UPDATE trigger pair.

    LVL 39

    Expert Comment

    try like this

    create a function as follows

    Create FUNCTION dbo.GetBranchCount
          @Code char(2)
    RETURNS int
          DECLARE @retVal int
          select @retVal=Count(1) from InternalSites where BranchID = @Code
          select @retVal= @retVal + Count(1) from ExternalSites where BranchID = @Code

          RETURN @retVal


    and add check constraint to your parts_master as follows

    ALTER TABLE [dbo].[Parts_Master]  WITH CHECK ADD  CONSTRAINT [CK_Parts_Master] CHECK  ((dbo.GetBranchCount([BranchID])<>0))
    ALTER TABLE [dbo].[Parts_Master] CHECK CONSTRAINT [CK_Parts_Master]
    LVL 42

    Accepted Solution

    If ExternalSites.BranchID and InternalSites.BranchID do not conflict, then simply add supertype table:





    If you are so inclined, you can also move columns that are common to both InternalSites and ExternalSites to the supertype table.


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now