September 20, 2012 at 3:39 am
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
September 20, 2012 at 4:41 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 20, 2012 at 4:46 am
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
September 20, 2012 at 5:53 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 26, 2012 at 4:11 am
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
September 26, 2012 at 4:51 am
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.
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
September 26, 2012 at 5:05 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply