Re: foreign key constraint versus referential integrity constraint
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 24 Oct 2009 20:07:09 -0300
Message-ID: <4ae38877$0$23775$9a566e8b_at_news.aliant.net>
>>Keith H Duggar wrote:
>>
>>>On Oct 22, 7:34 am, Sampo Syreeni <de..._at_iki.fi> wrote:
>>
>>>>>Thanks to everyone. I think I understand more clearly now the
>>>>>inherent asymmetry of foreign key constraints. But now then my
>>>>>question is what is the common name for the semantic constraint
>>>>>that "attribute RV1:X and RV2:Y are sematincally joinable"?
>>
>>>>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.
Date: Sat, 24 Oct 2009 20:07:09 -0300
Message-ID: <4ae38877$0$23775$9a566e8b_at_news.aliant.net>
Keith H Duggar wrote:
> On Oct 24, 3:13 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote: >
>>Keith H Duggar wrote:
>>
>>>On Oct 22, 7:34 am, Sampo Syreeni <de..._at_iki.fi> wrote:
>>
>>>>>Thanks to everyone. I think I understand more clearly now the
>>>>>inherent asymmetry of foreign key constraints. But now then my
>>>>>question is what is the common name for the semantic constraint
>>>>>that "attribute RV1:X and RV2:Y are sematincally joinable"?
>>
>>>>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.
> 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 - 01:07:09 CEST