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(at)debis(dot)com (Jan Wieck) #