Re: [HACKERS] RI generic trigger procs

Hannu Krosing wrote:
>
> Jan Wieck wrote:
> >
> >     Any combination of attributes in a table referenced to by one
> >     or  more  FOREIGN  KEY  ...  REFERENCES constraint of another
> >     table shall have a UNIQUE and NOT NULL constraint.
> ...
> >  So we assume here that any PK is unique and cannot contain NULL's.
>
> What is the reasoning behind requiring this ?
>
> I can't see anything that would mandate this -
>   * NULLs are'nt equal anyway and ar even disregarded under your
>     current description.
>     Or are you just protecting yourself against the case where the
>     foreign key field is set to null - could this be handled the
>     same as deleting for cascaded constraints ?

    MATCH  FULL (as I planned to implement for now) mandates that
    either none or all fields of foreign key are NULL.

    Of course, we could handle the UPDATE of referenced key  (PK)
    from  none  NULL  to  some/all  NULL  as if the operation was
    DELETE.  And similar treating  UPDATE/DELETE  where  OLD  had
    NULL(s)  as nothing, since according to MATCH FULL absolutely
    no reference can exist.

    When looking ahead it's better to add one  more  argument  to
    the  trigger  proc's  specifying  the MATCH type. That way we
    could add support for MATCH PARTIAL by only  working  on  the
    trigger  procs  with  no  need  to touch anything else in the
    system. This will be the 4th argument  before  the  attribute
    name pairs and containts either 'FULL' or 'PARTIAL'.

    Support  for  MATCH PARTIAL is alot more complicated though -
    thus I left it for later. Let's see how  fast  we  could  get
    this all to work and then decide if it's something to include
    in this or one of the next releases.

>   * UNIQUE would save us the check for existing other possible
>     referenced values - is this mandated by SQL spec ?

    SQL3 specification X3H2-93-359 and MUN-003

    11.9 <referential constraint definition>

          2) Case:

              a) If the <referenced table and columns> specifies a <reference
                column list>, then the set of column names of that <refer-
                ence column list> shall be equal to the set of column names
                in the unique columns of a unique constraint of the refer-
                enced table. Let referenced columns be the column or columns
                identified by that <reference column list> and let refer-
                enced column be one such column. Each referenced column shall
                identify a column of the referenced table and the same column
                shall not be identified more than once.

              b) If the <referenced table and columns> does not specify a
                <reference column list>, then the table descriptor of the
                referenced table shall include a unique constraint that spec-
                ifies PRIMARY KEY. Let referenced columns be the column or
                columns identified by the unique columns in that unique con-
                straint and let referenced column be one such column. The
                <referenced table and columns> shall be considered to implic-
                itly specify a <reference column list> that is identical to
                that <unique column list>.

    So the UNIQUE constraint on the  referenced  columns  of  the
    referenced table is mandatory.

    And  the  spec  also  tells  that the UNIQUE constrain on the
    referenced columns shall NOT be deferrable, so our (mis)usage
    of  a  unique  index  for  uniqueness doesn't break the specs
    here.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #