September 16, 2009 at 10:06 am
Have a table with references to two tables such that if FK1 has a value FK2 is null and vice versa, whats the best way to model these relationships?
September 17, 2009 at 5:19 am
[font="Verdana"]It won't be possible to insert record(s) in either the tables. Let us know the exact situation.
Mahesh[/font]
MH-09-AM-8694
September 17, 2009 at 9:08 am
the only way i could think of doing this was with a check cosntraint and a user defined function.
if you could have provided more details, we could have helped with a better example.
here's how i did it:
create function ThereCanBeOnlyOne(@FirstFK as int,@SecondFK as int)
RETURNS int
AS
BEGIN
DECLARE @results int
IF @FirstFK IS NULL AND @SecondFK IS NULL
SET @results = 0
IF @FirstFK IS NULL AND @SecondFK IS NOT NULL
SET @results = 0
IF @FirstFK IS NOT NULL AND @SecondFK IS NULL
SET @results = 0
IF @FirstFK IS NOT NULL AND @SecondFK IS NOT NULL
SET @results = 1
return @results
END
GO
CREATE TABLE [dbo].[PARENT] (
[PARENTID] INT IDENTITY(1,1) NOT NULL,
[PARENTTEXT] VARCHAR(30) NULL,
CONSTRAINT [PK__parent__1332DBDC] PRIMARY KEY CLUSTERED (parentid))
CREATE TABLE [dbo].[CHILD] (
[CHILDID] INT IDENTITY(1,1) NOT NULL,
[FIRSTFK] INT NULL,
[SECONDFK] INT NULL,
CONSTRAINT [CK__ONLYONEFKALLOWED] CHECK ([dbo].[ThereCanBeOnlyOne]([FIRSTFK],[SECONDFK])=(0)),
CONSTRAINT [FK__child__FIRSTFK__17F790F9] FOREIGN KEY (FIRSTFK) REFERENCES parent(parentid),
CONSTRAINT [FK__child__SECONDFK__18EBB532] FOREIGN KEY (SECONDFK) REFERENCES parent(parentid))
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply