So co-developers,

    let's go.

    I  think  the  best is if all RI triggers share the same call
    interface.  My favorite for now is this:

        RI_FKey_<operation>_<event> ( <args> )

    Where <operation> is one of check, cascade, restrict, setnull
    or  setdefault  and  where <event> is one of ins, upd or del.
    Thus, the trigger proc to check foreign key on  insert  would
    be  RI_FKey_check_ins()  and  the  one to do cascaded deletes
    becomes RI_FKey_cascade_del().

    The <args> is allways '<constraint_name>', '<FK_table_name>',
    '<PK_table_name>'    and    a   variable   length   list   of
    '<FK_attribute_name>', '<PK_attribute_name>'  pairs.  In  the
    case of an unnamed constraint, the <constraint_name> is given
    as '(unnamed)'. It is up to CREATE TABLE  parsing/utility  to
    build these arguments properly.

    All  the  procs  should  use  prepared  and  saved  SPI plans
    wherever possible.

    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.  I  don't
    think  that  it's  easy  to assure this and to avoid that the
    underlying unique index isn't dropped later. First  it  shall
    be   enough   to   document   and   leave   it   up   to  the
    creator/maintainer of the database schema.  So we assume here
    that any PK is unique and cannot contain NULL's.

    The  FOREIGN  KEY  itself  might  allow  NULL's, but since we
    implement only MATCH FULL right now, either all  or  none  of
    the  FK  attributes  may  contain  NULL.  We'll check this in
    RI_FKey_check_ins() and RI_FKey_check_upd().

    The behaviour of the procs in detail (this is what  ya  shall
    write - they don't exist):

    RI_FKey_check_ins()

        Implements  "FOREIGN  KEY  ...  REFERENCES ..." at insert
        time.

        Fired AFTER INSERT on FK table.

        First off, either all or none of the FK attributes in NEW
        must  be  NULL.   If all are NULL, nothing is checked and
        operation goes through.  Otherwise it raises an ERROR  if
        the given key isn't present in the PK table.

    RI_FKey_check_upd()

        Implements  "FOREIGN  KEY  ...  REFERENCES ..." at update
        time.

        Fired AFTER UPDATE on FK table.

        If all FK attributes in OLD and NEW are the same, nothing
        is  done.   Otherwise,  the  operation is the same as for
        RI_FKey_check_ins().

    RI_FKey_cascade_del()

        Implements "FOREIGN KEY ... ON DELETE CASCADE".

        Fired AFTER DELETE on PK table.

        It deletes all occurences of the deleted key  in  the  FK
        table.

    RI_FKey_cascade_upd()

        Implements "FOREIGN KEY ... ON UPDATE CASCADE".

        Fired AFTER UPDATE on PK table.

        Nothing  happens if OLD and NEW keys in PK are identical.
        Otherwise it updates all occurences of the OLD key to the
        NEW key in the FK table.

    RI_FKey_restrict_del()

        Implements "FOREIGN KEY ... ON DELETE RESTRICT".

        Fired AFTER DELETE on PK table.

        Checks if the deleted key is still referenced from the FK
        table and raises an ERROR if so.

    RI_FKey_restrict_upd()

        Fired AFTER UPDATE on PK table.

        Nothing happens if OLD and NEW keys in PK are  identical.
        Otherwise  checks if the OLD key is still referenced from
        the FK table and raises an ERROR if so.

    RI_FKey_setnull_del()

        Implements "FOREIGN KEY ... ON DELETE SET NULL"

        Fired AFTER DELETE on PK table.

        Updates all occurences of the OLD key to NULL  values  in
        the FK table.

    RI_FKey_setnull_upd()

        Implements "FOREIGN KEY ... ON UPDATE SET NULL"

        Fired AFTER UPDATE on PK table.

        Nothing  happens if OLD and NEW keys in PK are identical.
        Otherwise updates all occurences of the OLD key  to  NULL
        values in the FK table.

    RI_FKey_setdefault_del()

        Implements "FOREIGN KEY ... ON DELETE SET DEFAULT"

        Fired AFTER DELETE on PK table.

        Updates  all occurences of the OLD key in FK table to the
        default values defined in the schema of the FK table.

    RI_FKey_setdefault_upd()

        Implements "FOREIGN KEY ... ON UPDATE SET DEFAULT"

        Fired AFTER UPDATE on PK table.

        Nothing happens if OLD and NEW keys in PK are  identical.
        Otherwise  updates  all  occurences  of the OLD key in FK
        table to the default values defined in the schema of  the
        FK table.

    This  all  is  the  behaviour  of  FOREIGN KEY ... MATCH FULL
    according to the SQL3 standard - as I understood it.  I  know
    that  the  above  trigger procs aren't easy to implement. But
    after all, many of the  referential  action  ones  look  very
    similar to each other.

    One  general  thing required is IMHO some hashtable(s) living
    in the cache context where any trigger once fired  can  cache
    information  needed  again  and  again (like the saved plans,
    functions for equality checks on OLD vs. NEW, etc.).

    The bazar is open - come in.


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) #



Wed, 29 Sep 1999 20:47:18 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
RI generic trigger procs
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 ?
  * UNIQUE would save us the check for existing other possible 
    referenced values - is this mandated by SQL spec ?

-------------
Hannu

Wed, 29 Sep 1999 23:37:50 +0300
Hannu Krosing <hannu@trust.ee>
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) #



Wed, 29 Sep 1999 23:43:16 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] RI generic trigger procs