Thread: RE: [GENERAL] FOREIGN KEY syntax

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 }

[ .. ] means optional elements
{  .. | .. | ..} means exactly one of

Regards,
Andrzej Mazurkiewicz

andrzej@mazurkiewicz.org

www.mazurkiewicz.org


> -----Original Message-----
> From:    Ron Peterson [SMTP:rpeterson@yellowbank.com]
> Sent:    2 marca 2000 20:19
> To:    pgsql-general@postgreSQL.org
> Subject:    [GENERAL] FOREIGN KEY syntax
>
>
> I've downloaded and installed the 7.0 beta.  Works great so far!
>
> I noticed on the change list that foreign key support has been added.
> However, information about the use of this feature hasn't made it into
> the documentation yet.  Can anyone give me some examples of how to use
> FOREIGN KEY in postgresql?
>
> Ron Peterson
> rpeterson@yellowbank.com
>
> ************

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?

And what is MATCH FULL?

I take it that the 'List' in 'columList' implies that a foreign key can
span multiple columns?

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