[SOLVED] Supertype/Subtype relationship - disjointness constraint?

[dbo]

I've got a weird situation and I'm drawing a blank on the exact term. I think that it's called disjointness constraint (an entity can be a member of at most one of the subclasses of the specialization). I'll explain the situation and you can tell me what it's called if that's not correct

 

I think the easiest way to explain this is using an example. Assume that you have a generic EQUIPMENT table (parent). This just holds high level data on the equipment. A piece of equipment can be a BOAT (child), a CAR (child), or a TANK (child), but can never be more than one of these. This makes perfect sense in relational database theory... I'm unsure how to implement it in practice.

 

Question 1 - What the heck is this constraint called?

Question 2 - Is there a way to enforce this kind of relationship at the data model level or only via an application?

[dbo]

*bump* Any ideas?

[fenway]

I've never constrained this type of relationship in the DB...

[dbo]

I guess technically you could add a trigger to enforce it. It's one of those things in relational database theory that sounds good and makes sense... I've just never tried to put it into practice until now. I hate to have to rely on the application to maintain the data's integrity. I think it should be done at each level just like security.

[fenway]

I agree, but this outside the rather outdated scope of RDBMS set theory.

 

As long as your app controls the display, you can always run maintenance scripts to make sure nothing looks "funny".  Sanity checks, basically.

[dbo]

Thanks for the replies fenway.