Re: Modelling Disjoint Subtypes

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.


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.


then,

I want to ensure is that for each value of the attribute K appearing in COMMON, that value appears as the value for K in at most 1 of the tables SUBTYPE1...SUBTYPEN.

Indeed.
.

Relevant Pages

  • Re: Nullable Foreign key constriants
    ... nullable FK constraints a good database design practice? ... Nullability and foreign keys are two disstinct constraints, ... PersonID column as primary key and foreign key into the Persons table, ... So if you want to build a data model that is completely NULL free, ...
    (microsoft.public.sqlserver.programming)
  • A good reason for defining a one-to-one relationship
    ... diagram from visual studio 2005. ... In the database you just need to put a unique key on top of the foreign key ... relationship is impractical (or do the constraints only get checked on ... SQL Server constraints get checked at the end of each statement, ...
    (microsoft.public.sqlserver.server)
  • Re: Relationships. Does anyone use them?
    ... more overhead in creating an app and unnecessary...or do you believe ... One of the basic precepts of relational database design is to have the ... generally called constraints - I believe that is the term used in the ... One important constraint is what in Oracle is called the foreign key ...
    (comp.databases.ms-access)
  • Re: Disable relationships
    ... ALTER TABLE DISABLE CONSTRAINTS ALL ... We have a table that indicates the people in the SQL Server 2K ... > painful with the current foreign key relationships we have. ...
    (microsoft.public.sqlserver.programming)
  • Re: Multiplicity, Change and MV
    ... code required to enforce constraints must exist in each and every ... those common app-constraints should not be embedded ... in the db-engine itself but in a layer just above it within the DBMS ... Data types are not available until created just like types ...
    (comp.databases.theory)