[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • 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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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