InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 18:10:17 GMT20http://www.sqlservercentral.com/Forums/Topic1361819-392-1.aspx[quote] Is it possible to have a column on table A that references table B or table C depending on another column on table A? [/quote]This is a very bad design and violates First Normal Form (1NF). It would mean that the column in A is overloaded; it is [i]two different[/i] 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 [i]to be nothing at all.[/i]” 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.Wed, 26 Sep 2012 07:48:23 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1361819-392-1.aspx[quote][b]ChrisM@Work (9/26/2012)[/b][hr]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.[/quote]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,PedroWed, 26 Sep 2012 05:05:52 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1361819-392-1.aspx[quote][b]PiMané (9/20/2012)[/b][hr]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[/quote]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.Wed, 26 Sep 2012 04:51:43 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1361819-392-1.aspx[quote][b]Grant Fritchey (9/20/2012)[/b][hr]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.[/quote]Hi,Just made a little search and found an article on conditional check constraints... Are they "better" than the trigger solution?[code="sql"]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))GOCREATE FUNCTION CheckEntityExists(@EntityType INT, @EntityId INT) RETURNS INT ASBEGIN 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 @retENDGOALTER TABLE GeneralInfo ADD CONSTRAINT CheckEntityExistsConstraint CHECK (dbo.CheckEntityExists(EntityType, EntityId) > 0)GOINSERT 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"..[/code]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,PedroWed, 26 Sep 2012 04:11:53 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1361819-392-1.aspxWell, 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.Thu, 20 Sep 2012 05:53:03 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1361819-392-1.aspx[quote][b]Grant Fritchey (9/20/2012)[/b][hr]Nope.You'd have to write a trigger to do something like that.[/quote]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,PedroThu, 20 Sep 2012 04:46:27 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1361819-392-1.aspx[quote][b]PiMané (9/20/2012)[/b][hr]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[/quote]Nope.You'd have to write a trigger to do something like that.Thu, 20 Sep 2012 04:41:50 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1361819-392-1.aspxHi,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,PedroThu, 20 Sep 2012 03:39:23 GMTPiMané