Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

FK referencing multiple tables Expand / Collapse
Author
Message
Posted Thursday, September 20, 2012 3:39 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
Hi,

Is it possible to have a column on table A that references table B or table C depending on another column on table A?
For example:
EntityAddresses (EntityAddressId, EntityType, EntityID, Address)
Customers (CustomerID, ....)
Suppliers (SupplierID, ....)

Can EntityID be a FK to both CustomerID and SupplierID but only check the value in CustomerID if EntityType is 0 and SupplierID if EntityType is 1?

Thanks,
Pedro




If you need to work better, try working less...
Post #1361819
Posted Thursday, September 20, 2012 4:41 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 15,621, Visits: 28,003
PiMané (9/20/2012)
Hi,

Is it possible to have a column on table A that references table B or table C depending on another column on table A?
For example:
EntityAddresses (EntityAddressId, EntityType, EntityID, Address)
Customers (CustomerID, ....)
Suppliers (SupplierID, ....)

Can EntityID be a FK to both CustomerID and SupplierID but only check the value in CustomerID if EntityType is 0 and SupplierID if EntityType is 1?

Thanks,
Pedro


Nope.

You'd have to write a trigger to do something like that.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1361865
Posted Thursday, September 20, 2012 4:46 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
Grant Fritchey (9/20/2012)

Nope.

You'd have to write a trigger to do something like that.


If the insert/update is made by a SP what would be better: change the SP the check if the referenced value exists or write the trigger? If the trigger was used what would be the most appropriate: BEFORE, INSTEAD, FOR or AFTER (don't think AFTER makes any sense here..).

Thanks,
Pedro




If you need to work better, try working less...
Post #1361869
Posted Thursday, September 20, 2012 5:53 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 15,621, Visits: 28,003
Well, an SP is going to do the code correctly, yes. But the idea behind a constraint is to make sure things are enforced, regardless of where the code is sourced. If you really need that kind of enforcement, just a straight trigger would be the way to go.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1361886
Posted Wednesday, September 26, 2012 4:11 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
Grant Fritchey (9/20/2012)
Well, an SP is going to do the code correctly, yes. But the idea behind a constraint is to make sure things are enforced, regardless of where the code is sourced. If you really need that kind of enforcement, just a straight trigger would be the way to go.

Hi,

Just made a little search and found an article on conditional check constraints...
Are they "better" than the trigger solution?
CREATE TABLE GeneralInfo (EntityType INT, EntityId INT, CONSTRAINT PK_GeneralInfo PRIMARY KEY (EntityType, EntityId))
CREATE TABLE Customers (Id INT, Name VARCHAR(50), CONSTRAINT PK_Customers PRIMARY KEY (Id))
CREATE TABLE Suppliers (Id INT, Name VARCHAR(50), CONSTRAINT PK_Suppliers PRIMARY KEY (Id))
GO

CREATE FUNCTION CheckEntityExists(@EntityType INT, @EntityId INT) RETURNS INT AS
BEGIN
DECLARE @ret INT = 0
SELECT @ret = COUNT(*) FROM (SELECT 1 Record FROM Customers WHERE @EntityType = 0 AND Id = @EntityId UNION ALL SELECT 1 FROM Suppliers WHERE @EntityType = 1 AND Id = @EntityId) t
RETURN @ret
END
GO

ALTER TABLE GeneralInfo ADD CONSTRAINT CheckEntityExistsConstraint CHECK (dbo.CheckEntityExists(EntityType, EntityId) > 0)
GO

INSERT INTO GeneralInfo (EntityType, EntityId) VALUES (0, 1)
GO
--ERROR:
--Msg 547, Level 16, State 0, Line 1
--The INSERT statement conflicted with the CHECK constraint "CheckEntityExistsConstraint". The conflict occurred in database "DBA_Teste", table "dbo.GeneralInfo".
INSERT INTO Customers (Id, Name) VALUES (1, 'Customer 1')
INSERT INTO GeneralInfo (EntityType, EntityId) VALUES (0, 1)
-- OK.
DELETE FROM Customers
-- OK. Should not be allowed to delete since the record is "referenced"..

But there's a problem with this conditional constraints... or I just don't know how to do it (most probably)... I can delete from the customers table and have "bad" data on GeneralInfo table...
Is there a way to add a condition check constraint on delete, or just like the questions says "ON DELETE", the only way to do this is with a ON DELETE trigger on the Customers table?

Thanks,
Pedro




If you need to work better, try working less...
Post #1364549
Posted Wednesday, September 26, 2012 4:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:04 AM
Points: 7,175, Visits: 13,619
PiMané (9/20/2012)
Hi,

Is it possible to have a column on table A that references table B or table C depending on another column on table A?
For example:
EntityAddresses (EntityAddressId, EntityType, EntityID, Address)
Customers (CustomerID, ....)
Suppliers (SupplierID, ....)

Can EntityID be a FK to both CustomerID and SupplierID but only check the value in CustomerID if EntityType is 0 and SupplierID if EntityType is 1?

Thanks,
Pedro


It's not that uncommon to have a business entity which is both a supplier and a customer to your business. With your relationships modelled in this direction, you'd have to duplicate an address.
If address is an attribute of customer (or supplier), you don't. You also don't have this problem of a key having more than one meaning.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1364562
Posted Wednesday, September 26, 2012 5:05 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
ChrisM@Work (9/26/2012)

It's not that uncommon to have a business entity which is both a supplier and a customer to your business. With your relationships modelled in this direction, you'd have to duplicate an address.
If address is an attribute of customer (or supplier), you don't. You also don't have this problem of a key having more than one meaning.


Addresses are another table with no direct relation to entities... A EntityAddresses (EntityType, EntityId, AddressId) makes that relation.
But in the kind of modelling it's very hard to have FK since the reference is always multiple fields... so I either have check constraints or triggers...

Thanks,
Pedro




If you need to work better, try working less...
Post #1364571
Posted Wednesday, September 26, 2012 7:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:00 AM
Points: 1,945, Visits: 2,884
Is it possible to have a column on table A that references table B or table C depending on another column on table A?


This is a very bad design and violates First Normal Form (1NF). It would mean that the column in A is overloaded; it is two different kinds of attributes in one column. You will see the term “Automobiles, Squids and Lady Gaga” tables and procedures for things are improperly designed like this.

Look at your own example. Think about how insanely generic “entity” is! This violates a fundamental principle of logic, The Law of Identity which says “To be is to be something in particular; to be nothing in particular or everything in general is to be nothing at all.

In procedural code, the counterpart of this design error is flag coupling where the meaning of a code module is controlled by a flag from outside the module.

Looking at your postings, you need to get a good book on data modeling.



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1364686
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse