Re: Modelling Disjoint Subtypes

Bob Badour wrote:
Joe Thurbon wrote:

Bob Badour wrote:

Joe Thurbon wrote:

Am I just barking up the wrong tree? Would you like to tell me the answer?

You are barking up the wrong tree. If at all possible, the only constraints one should have are foreign key references. What exactly prevents one from using a foreign key constraint to enforce the disjoint requirement?

A lack of imagination on my part?

More seriously, I think the answer is that a foreign key constraint enforces a constraint between columns in two tables. The disjoint entity requirement requires a constraint to be enforced between columns in N tables (where there are N-1 subtypes).

The foreign key constraints for N-1 subtypes require N-1 foreign key constraints.

If one uses foreign key constraints to enforce disjointedness among the same subtypes, presumably one may use at least as many foreign key constraints.


Fair enough.


Or, more explicitly, if
- a table COMMON contains columns that are common to a number of entities, and COMMON has a primary key K; and
- tables SUBTYPE1 ... SUBTYPEN represent specialisations of COMMON, these tables also have a primary key K which references K as a foreign key.

Remember: K is a set of attributes.


Do you mean something like this (for two sub-types):

Domain of ID is integer
Domain of Type is {SubOneType, SubTwoType}

Super
ID (primary key)
Type (may be any of SubOneType, SubTwoType)

SubOne
ID (primary key, references Super.ID)
Type (must be SubOneType, references Super.Type)

SubTwo
ID (primary key, references Super.ID)
Type (must be SubTwoType, referenecs Super.Type)

where, SubOne(ID, Type) has to reference a Super(ID, Type) and thus no SubTwo could reference the same ID because the Type would be incorrect?

Hmm, that seems more elegant!

But is it what you were intending?

This also seems to be a situation where the often recommended practice of combining composite keys into a surrogate simple key would not work, since you're relying on the elements of the composite key to do some constraint enforcement.

Interesting.

Cheers,
Joe

.

Relevant Pages

  • Re: Create Table Issues
    ... unique index on BookID. ... > CONSTRAINT PubPK PRIMARY KEY ... > CONSTRAINT PubFK FOREIGN KEY REFERENCES Publisher ...
    (microsoft.public.access.gettingstarted)
  • Problem merging two queries
    ... CONSTRAINT PRIMARY KEY CLUSTERED ... REFERENCES ( ... CONSTRAINT FOREIGN KEY ...
    (microsoft.public.sqlserver.programming)
  • Re: Foreign key problem
    ... primary key of the parent table columninfo. ... denormalisation, because the table_name is not redundant: ... One can therefore forget that a foreign key is nothing more than a ... In TTM's Algebra I imagine the constraint might be something like: ...
    (comp.databases.theory)
  • Re: New to SQL server
    ... [CONSTRAINT constraint_name] ... | [FOREIGN KEY] ... Is the name of the database in which the table is created. ... REFERENCES permission on the type is ...
    (microsoft.public.access.adp.sqlserver)
  • RE: Foreign Key
    ... that this is not as common as a foreign key referencing a different table, ... To view all employees who work ... CONSTRAINT emp_last_name_nn NOT NULL ... REFERENCES jobs ...
    (microsoft.public.data.ado)