Re: foreign key constraint versus referential integrity constraint

From: Sampo Syreeni <>
Date: Sun, 25 Oct 2009 15:47:08 -0700 (PDT)
Message-ID: <>

> >>>>Hmm. Usually we talk about constraints when they're syntactic, because
> >>>>that's what makes it possible to enforce them automatically, simply by
> >>>>manipulating the symbols in the database. So what you actually mean
> >>>>is, what syntactic construct is appropriate for enforcing the
> >>>>semantics you describe. I'd argue that that's what domains were
> >>>>invented for.
> >>>I agree that it is possible to enforce such join semantics with
> >>>domains but I'm not sure it is entirely appropriate. I say that
> >>>because to the greatest extent the RM is orthogonal to domains.
> >>>To quote CJ Date "Databases In Depth" Chapter 2 Summary:
> >>>"It's a very common misconception that the relational model deals
> >>>only with rather simple types: numbers, strings, perhaps dates
> >>>and times, and not much else. In this chapter, I've tried to show
> >>>that this is indeed a misconception. Rather, relations can have
> >>>attributes of /any type whatsoever/ -- the relational model
> >>>nowhere predicates what those types must be, and in fact they
> >>>can be as complex as we like ... In other words, the question as
> >>>to what types are supported is orthogonal to the question of
> >>>support for the relational model itself. Or (less precisely but
> >>>more catchily): /types are orthogonal to tables/."
> >>>To put this another way, the RM should be just as complete and
> >>>effective for a universe with a single domain as it is for a
> >>>universe with a rich domain set (such as you propose to handle
> >>>my GenericID join constraint example). And placing constraints
> >>>on /relational expressions/ seems as fundamental to the RM as
> >>>placing constraints on /relational values/.
> >>>By pushing such expression constraints off to domains, we are
> >>>admitting that the RM itself has no support for constraining
> >>>relational expressions and I'm not prepared to admit that
> >>>limitation. Especially since it does provide support for
> >>>constraining the values of relational variables.
> >>>I will also note that both Codd and Date proposed RM mechanisms
> >>>to operate the other way around ie to allow designers to force
> >>>cross-domain operations. Codd with "Domain Check Overrides" and
> >>>date with the THE_ operators that provide type coercion.
> >>Domains, however, define what is or is not joinable because natural join
> >>applies the equality comparison to attributes with like names, and
> >>domains define equality comparisons.
> > Domains also define a set of permissible values for attributes.
> > However, that doesn't stop RM from adding additional constraints
> > to limit those values further.
> You asked about semantic joinability. Semantically, the join works if
> like-named attributes have an equality comparison defined and not if
> they don't.
> > Why should relational operations
> > be any different in this respect from relational variables? Ie
> > why shouldn't the RM allow us to further constrain what domains
> > permit for both values AND operations?
> Because the RM doesn't have anything much to say about domains. Domains
> are a separate--albeit vitally important--concern.
Received on Sun Oct 25 2009 - 23:47:08 CET

Original text of this message