• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

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




0
skiltz
Asked:
skiltz
1 Solution
 
JimBrandleyCommented:
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
0
 
appariCommented:
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]
0
 
dqmqCommented:
If ExternalSites.BranchID and InternalSites.BranchID do not conflict, then simply add supertype table:

Sites
  BranchID(PK)

InternalSites
  BranchID(PK,FK-->Sites.BranchID)

ExternalSites
  BranchID(PK,FK-->Sites.BranchID)

Parts_Master
  BranchID(FK-->Sites.BranchID)


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


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now