Foreign Key Constraint Against Two Other Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Foreign Key Constraint Against Two Other Tables

Author

Topic

unclesamis@cox.net
Starting Member

18 Posts

Posted - 2008-12-31 : 00:20:35

-- I'm trying to get one table to allow a foreign key constraint of one column to a primary key of two tables each
-- Any advice on how to get this to work is greatly appreciated.

-- TABLE ONE - with a primary key
CREATE TABLE PersonTypeA (ID INT IDENTITY(1,1) PRIMARY KEY, FirstName VARCHAR(20))

-- TABLE TWO - with a primary
CREATE TABLE PersonTypeB (ID INT IDENTITY(-1,-1) PRIMARY KEY, FirstName VARCHAR(20))

-- TABLE NEEDING TO HAVE A FOREIGN KEY CONSTRAINT TO BOTH ABOVE TABLES
CREATE TABLE Dwelling(ID INT, TypeOf VARCHAR(20))

-- CREATE FK CONSTRAINT HERE
ALTER TABLE Dwelling
ADD CONSTRAINT fk_Dwelling_Person_ID
FOREIGN KEY (ID)
REFERENCES PersonTypeA (ID)

-- INSERT VALUE IN TABLE ONE
INSERT INTO PersonTypeA(FirstName)
VALUES('Andy')

-- INSERT VALUE IN TABLE TWO
INSERT INTO PersonTypeB(FirstName)
VALUES('Bob')

-- INSERT VALUE INTO TABLE HAVING FK TO TABLE ONE
INSERT INTO Dwelling(ID, TypeOf)

-- CAN'T GET THIS VALUE IN BECAUSE IT DOESN'T MATCH A PRIMARY KEY IN TABLE ONE
INSERT INTO Dwelling(ID, TypeOf)
VALUES(-1,'Apartment')
VALUES(1,'House')

-- CAN'T SEEM TO PUT A FOREIGN KEY CONSTRAINT ON THE OTHER TABLE - PROBABLY BECAUSE THIS COLUMN IS A FK KEY TO TABLE ONE ALREADY
ALTER TABLE Dwelling
ADD CONSTRAINT fk_Dwelling_Person_ID_2
FOREIGN KEY (ID)
REFERENCES PersonTypeB (ID)

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-31 : 02:20:19

To my knowledge i think it is not possible for a foreign key to refer two primary keys of two different tables

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 03:12:28

as a workaround,what you can do is to create two columns in your main table each of which is linked to one of two tables by means of foreign key.Then create a check contraint on your table for condition col1 is not null or col2 is not null. this will ensure under any scenario at least one of your two columns will have a value which will be a valid value in corresponding table to which its linked via foreign key.

unclesamis@cox.net
Starting Member

18 Posts

Posted - 2008-12-31 : 16:28:07

Thanks for the input visakh16.

Problem is the application and code have been around for years and adding this new column would require a lot of alteration for displaying in app and conditions in the procedures.

Everything is set up to display from only the ID column of the main (Dwelling) table; inclusion to the several other tables similar to this one.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-12-31 : 22:31:29

if you can't do what visakh suggested, you could implement this with a trigger.

elsasoft.org

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-01 : 13:32:27

quote:
Originally posted by jezemine

if you can't do what visakh suggested, you could implement this with a trigger.

elsasoft.org

yup...thats also a solution. but use of trigger might have an impact on performance.

unclesamis@cox.net
Starting Member

18 Posts

Posted - 2009-01-05 : 02:51:20

Thanks for your input jezemine

A trigger for this situation was a performance issue that I was intending on avoiding; as it would be spread across many tables.

unclesamis@cox.net
Starting Member

18 Posts

Posted - 2009-01-07 : 21:03:51

I would like to thank everyone for all the good advice in helping aid my dilemma:

I found a solution that works just perfectly.

1. I put NOCHECK constraints on both of the PersonType tables: Maintaining the relationship.
2. Created a function that would test if the supplied value (ID) could be found in either table.
3. And finally put CHECK Constraints on both tables using the function; allowing values that return true.

Example below:
-------------------------------------------------------------------------------------------------------------

-- ALTER THE CONSTRAINT ASSOCIATED TO PERSON TYPE A SO THAT IT DOESN'T ENFORCE CONSTRAINT
ALTER TABLE dbo.Dwelling
NOCHECK CONSTRAINT fk_Dwelling_Person_ID

-- ADD A NO CHECK CONSTRAINT ASSOCIATED TO PERSON TYPE B
ALTER TABLE Dwelling WITH NOCHECK
ADD CONSTRAINT fk_Dwelling_PersonB_ID
FOREIGN KEY (ID)
REFERENCES PersonTypeB (ID)

-- CREATE A VIEW COMBINING ALL VALUES FROM BOTH PERSON TYPE A AND PERSON TYPE B
CREATE VIEW vPersonType AS
SELECT ID, FirstName
FROM PersonTypeA
UNION
SELECT ID, FirstName
FROM PersonTypeB

-- CREATE A FUNCTION FOR TESTING ID VALUE AGAINST VIEW PERSON TYPE
CREATE FUNCTION dbo.udf_CheckIDConstraint(@ID INT)
RETURNS BIT
AS
BEGIN
IF EXISTS(
SELECT ID
FROM vPersonType
WHERE ID = @ID)
RETURN 1
RETURN 0
END

-- CREATE CHECK CONSTRAINT FOR PERSON TYPE A USING CHECK CONSTRAINT FUNCTION
ALTER TABLE PersonTypeA
ADD CONSTRAINT chk_PersonTypeA_Constraint_ID CHECK (dbo.udf_CheckIDConstraint(ID) = 1)

-- CREATE CHECK CONSTRAINT FOR PERSON TYPE B USING CHECK CONSTRAINT FUNCTION
ALTER TABLE PersonTypeB
ADD CONSTRAINT chk_PersonTypeB_Constraint_ID CHECK (dbo.udf_CheckIDConstraint(ID) = 1)

-- NOW I CAN GET THIS VALUE IN BECAUSE OF THE NEW CONSTRAINT
INSERT INTO Dwelling(ID, TypeOf)
VALUES(-1,'Apartment')

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2009-01-07 : 23:24:12

not clear to me that calling a UDF from a constraint is any better than a trigger - the perf of either one will likely be bad.

elsasoft.org

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 03:18:34

quote:
Originally posted by jezemine

not clear to me that calling a UDF from a constraint is any better than a trigger - the perf of either one will likely be bad.

elsasoft.org

no need of udf you can just do check inline in add constraint statement

unclesamis@cox.net
Starting Member

18 Posts

Posted - 2009-01-23 : 15:29:06

jezemine:
not clear to me that calling a UDF from a constraint is any better than a trigger - the perf of either one will likely be bad.

Thanks for your input jezemine

The performance doesn't pose an issue using the UDF, not that I compared a trigger performance, the fk constraint would act relatively the same if it could only check values from either table.

unclesamis@cox.net
Starting Member

18 Posts

Posted - 2009-01-23 : 15:33:57

visakh16:
no need of udf you can just do check inline in add constraint statement

Thanks for your input visakh16:

I would only get errors trying to do something as such.
Do you have an example of a working statement?

unclesamis@cox.net
Starting Member

18 Posts

Posted - 2009-01-23 : 15:41:46

-- had an error in the initial code posting, should be:

-- TABLE ONE - with a primary key
CREATE TABLE PersonTypeA (ID INT IDENTITY(1,1) PRIMARY KEY, FirstName VARCHAR(20))

-- TABLE TWO - with a primary
CREATE TABLE PersonTypeB (ID INT IDENTITY(-1,-1) PRIMARY KEY, FirstName VARCHAR(20))

-- TABLE NEEDING TO HAVE A FOREIGN KEY CONSTRAINT TO BOTH ABOVE TABLES
CREATE TABLE Dwelling(ID INT, TypeOf VARCHAR(20))

-- CREATE FK CONSTRAINT HERE
ALTER TABLE Dwelling
ADD CONSTRAINT fk_Dwelling_Person_ID
FOREIGN KEY (ID)
REFERENCES PersonTypeA (ID)

-- INSERT VALUE IN TABLE ONE
INSERT INTO PersonTypeA(FirstName)
VALUES('Andy')

-- INSERT VALUE IN TABLE TWO
INSERT INTO PersonTypeB(FirstName)
VALUES('Bob')

-- INSERT VALUE INTO TABLE HAVING FK TO TABLE ONE
INSERT INTO Dwelling(ID, TypeOf)
VALUES(1,'House')

-- CAN'T GET THIS VALUE IN BECAUSE IT DOESN'T MATCH A PRIMARY KEY IN TABLE ONE
INSERT INTO Dwelling(ID, TypeOf)
VALUES(-1,'Apartment')

-- CAN'T SEEM TO PUT A FOREIGN KEY CONSTRAINT ON THE OTHER TABLE - PROBABLY BECAUSE THIS COLUMN IS A FK KEY TO TABLE ONE ALREADY
ALTER TABLE Dwelling
ADD CONSTRAINT fk_Dwelling_Person_ID_2
FOREIGN KEY (ID)
REFERENCES PersonTypeB (ID)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-24 : 13:40:54

what i told was this




-- TABLE ONE - with a primary key
CREATE TABLE PersonTypeA (ID INT IDENTITY(1,1) PRIMARY KEY, FirstName VARCHAR(20))

-- TABLE TWO - with a primary
CREATE TABLE PersonTypeB (ID INT IDENTITY(-1,-1) PRIMARY KEY, FirstName VARCHAR(20))

-- TABLE NEEDING TO HAVE A FOREIGN KEY CONSTRAINT TO BOTH ABOVE TABLES
CREATE TABLE Dwelling(IDA INT,IDB INT, TypeOf VARCHAR(20))

-- CREATE FK CONSTRAINT HERE
ALTER TABLE Dwelling 
ADD CONSTRAINT fk_Dwelling_PersonA_ID 
FOREIGN KEY (IDA) 
REFERENCES PersonTypeA (ID)




--CREATE SECOND FK
ALTER TABLE Dwelling 
ADD CONSTRAINT fk_Dwelling_PersonB_ID 
FOREIGN KEY (IDB) 
REFERENCES PersonTypeB (ID)

--create the check constraint
ALTER TABLE Dwelling 
ADD CONSTRAINT Chk_Dwelling_ID
CHECK (IDA IS NOT NULL OR IDB IS NOT NULL)

--now insert data

-- INSERT VALUE IN TABLE ONE
INSERT INTO PersonTypeA(FirstName)
VALUES('Andy')

-- INSERT VALUE IN TABLE TWO
INSERT INTO PersonTypeB(FirstName)
VALUES('Bob')

-- INSERT VALUE INTO TABLE HAVING FK TO TABLE ONE
INSERT INTO Dwelling(ID, TypeOf)
VALUES(1,'House')


INSERT INTO Dwelling(ID, TypeOf)
VALUES(-1,'Apartment')

unclesamis@cox.net
Starting Member

18 Posts

Posted - 2009-01-24 : 21:38:42

Thanks for your posting visakh16

Though this works fine for this small example, I have hundreds of tables similar to this Dwelling table. I would have to make many modifications - something of which I was trying to avoid. Not only would I have to modify the tables, but also the procs, views, and functions. The solution I used appears to be the least work. Not saying that I should always go with the quickest solution, but it appears as if the solution I used is working just as I need it too; having the bonus of least alteration.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-25 : 04:53:48

Ok...then its upto you.The solution i suggested was one i have used couple of times in similar cases

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources