asked on

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

-- 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

JimBrandley

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.

Jim

appari

try like this

create a function as follows

Create FUNCTION dbo.GetBranchCount
(
      @Code char(2)
)
RETURNS int
AS
BEGIN
      DECLARE @retVal int

      select @retVal=Count(1) from InternalSites where BranchID = @Code
      select @retVal= @retVal + Count(1) from ExternalSites where BranchID = @Code

      RETURN @retVal

END

and add check constraint to your parts_master as follows

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

ASKER CERTIFIED SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial