Postgres Pro
Downloads
Home   >   mailing lists

Thread: RE: [GENERAL] FOREIGN KEY syntax

RE: [GENERAL] FOREIGN KEY syntax

From
Andrzej Mazurkiewicz
Date:

> -----Original Message-----
> From:    Ron Peterson [SMTP:rpeterson@yellowbank.com]
> Sent:    3 marca 2000 15:55
> To:    Andrzej Mazurkiewicz
> Cc:    pgsql-general@postgreSQL.org
> Subject:    Re: [GENERAL] FOREIGN KEY syntax
>
> Andrzej Mazurkiewicz wrote:
> >
> > The following information is based on gram.y file of postgres sources
> >
> > FOREIGN KEY ( columnList ) REFERENCES tableName [ ( columnList) ] [MATCH
> > FULL] [ON DELETE action] [ON UPDATE action]
> >
> > [[NOT] DEFERRABLE] [INITIALLLY {IMMEDIATE | DEFERRED}] (not all matches
> are
> > allowed)
> >
> > "action" is  {NO ACTION | RESTRICT | CASCADE | SET NULL_P | SET DEFAULT
> }
>
> What does action RESTRICT do?  Disallow the update or delete?
    [Andrzej Mazurkiewicz]  Disallows to delete master table row if
there are detail records in FOREIGN KEY relation. Disallows to modify
referenced columns in master row it there is at least one detail row in
FOREIGN KEY relation with that master row.

> And what is MATCH FULL?
    [Andrzej Mazurkiewicz] I would not like to speculate perhaps
somebody else knows exact answer.

> I take it that the 'List' in 'columList' implies that a foreign key can
> span multiple columns?
    [Andrzej Mazurkiewicz]  YES. my understanding is that the second
column list means not only primary key can be referenced but any other
combination of columns as far as it is unique in the master table..

> I did sucessfully create some tables with foreign keys using CREATE
> TABLE and ALTER TABLE.  That's as far as I've gotten yet, though.  Maybe
> when I get some various working examples I'll post them back.  Here's
> one example (in case anyone's interested):
>
> CREATE TABLE contact (
>     contact_id    SERIAL
>             PRIMARY KEY,
>     name_sort    varchar(80),
>     name_print    varchar(80),
>     created        timestamp default current_timestamp,
>     FOREIGN KEY (address_id)
>     REFERENCES address(address_id)
>     ON DELETE CASCADE
> );
>
> Thanks for help.
>
> Ron Peterson
> rpeterson@yellowbank.com

Re: [GENERAL] FOREIGN KEY syntax

From
"Oliver Elphick"
Date:
Andrzej Mazurkiewicz wrote:
  >> And what is MATCH FULL?
  >    [Andrzej Mazurkiewicz] I would not like to speculate perhaps
  >somebody else knows exact answer.

MATCH FULL:
  Either all referencing columns must be null, or all must have values.

MATCH PARTIAL:
  "if any columns are non-null, there must be at least one row in the
   referenced table that could satisfy the constraint if the other null
   values were correctly substituted."
                           (The SQL Standard Handbook, Cannan & Otten)
--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "A new commandment I give to you, that you love one
      another, even as I have loved you."
                                        John 13:34

Re: [GENERAL] FOREIGN KEY syntax

From
Bruce Momjian
Date:
Oliver Elphick wrote:

> Andrzej Mazurkiewicz wrote:
>   >> And what is MATCH FULL?
>   >    [Andrzej Mazurkiewicz] I would not like to speculate perhaps
>   >somebody else knows exact answer.
>
> MATCH FULL:
>   Either all referencing columns must be null, or all must have values.
>
> MATCH PARTIAL:
>   "if any columns are non-null, there must be at least one row in the
>    referenced table that could satisfy the constraint if the other null
>    values were correctly substituted."

    As I read the SQL3 spec draft, it's that if at least one
    of the columns is NULL, no check is done at all.

    Could be wrong, will lookup at Thursday when back in
    Hamburg.


Jan from Philly


--
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [GENERAL] FOREIGN KEY syntax

From
"Oliver Elphick"
Date:
Bruce Momjian wrote:
  >Oliver Elphick wrote:
  >
  >> Andrzej Mazurkiewicz wrote:
  >>   >> And what is MATCH FULL?
  >>   >    [Andrzej Mazurkiewicz] I would not like to speculate perhaps
  >>   >somebody else knows exact answer.
  >>
  >> MATCH FULL:
  >>   Either all referencing columns must be null, or all must have values.
  >>
  >> MATCH PARTIAL:
  >>   "if any columns are non-null, there must be at least one row in the
  >>    referenced table that could satisfy the constraint if the other null
  >>    values were correctly substituted."
  >
  >    As I read the SQL3 spec draft, it's that if at least one
  >    of the columns is NULL, no check is done at all.

What I quoted was based on SQL92.  Perhaps it has been changed?

  >    Could be wrong, will lookup at Thursday when back in
  >    Hamburg.
  >
  >
  >Jan from Philly

Is this really you, Bruce?


--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "Go ye therefore, and teach all nations, baptizing them
      in the name of the Father, and of the Son, and of the
      Holy Ghost; Teaching them to observe all things
      whatsoever I have commanded you; and, lo, I am with
      you alway, even unto the end of the world. Amen."
                     Matthew 28:19,20