May 31, 2008 at 5:30 pm
Is there a way to ensure that records in a "parent" table may be distributed among several "child" tables, such that no "parent" IDs are duplicated among the "child" tables?
I have Equipment of many types, each of which has a unique set of properties.
I'd like to house each Type in its own table, ensuring that there is no duplication of Equipment IDs in these "child" tables.
I assume a mapping table or two, but am struggling with a design.
Many thanks for any help.
May 31, 2008 at 7:39 pm
If you want to ensure that ParentID of 3 is only used in TableA and not TableB or TableC, this isn't technically referential integrity. Referential integrity is about relationships between two particular tables. Therefore, the use of foreign keys here won't work.
About the only way to do this is in the application code itself (such as the stored procedure) or via a trigger where you check via IF EXISTS.
K. Brian Kelley
@kbriankelley
May 31, 2008 at 9:50 pm
Thanks very much. I'll work on one of the options you mentioned.
May 31, 2008 at 10:29 pm
Well, let me ask what may be a dumb question... Are you sure the child tables can't be combined in some way? Maybe even with the parent table? It sounds like an awfully strange data structure to have the conditions you've specified.
K. Brian Kelley
@kbriankelley
June 2, 2008 at 11:38 am
I'd use "table inheritance". For example, you have customers and employees, both persons, both have different set of properties, but they have some common properties.
create table dbo.person(
id integer primary key,
name varchar(63),
--other fields
)
go
create table dbo.employee(
person_id integer references dbo.person(id),
--other fields
)
go
create unique index on dbo.employee(person_id)
go
Same for Customer. Add some trigger logic to ensure that a person is either employee or customer, if needed.
June 2, 2008 at 12:25 pm
I'm going to second Robert's suggestion. One master table with the IDs, then child tables with the ID and other stuff. Triggers can be used to enforce unique values.
Of course, that's assuming that combining the tables isn't possible. That would be the best solution.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 2, 2008 at 1:58 pm
One option without triggers would be to add a TableName or TableID column to the parent and each child table. In each child table, this tablename or tableID column would be a single value controlled by a default and check constraint. Each child table would need a unique value. Create the FK on Parent ID and TableID which would then ensure that the parent ID only existed in one child table. You can still keep the ID as the PK in the child tables, just create a unique key based on ID and TableID.
When you query the parent table, you will know which table the child information is stored by reading the TableName or TableID column.
David
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply