FW: FOREIGN KEY syntax

FYI

> -----Original Message-----
> From:    Andrzej Mazurkiewicz
> Sent:    3 marca 2000 17:05
> To:    'Ron Peterson'
> Cc:    pgsql-general@postgreSQL.org
> Subject:    RE: [GENERAL] FOREIGN KEY syntax
>
>
>
> -----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