RE: [GENERAL] FOREIGN KEY syntax

> -----Original Message-----
> From: Ron Peterson [SMTP:rpeterson(at)yellowbank(dot)com]
> Sent: 3 marca 2000 15:55
> To: Andrzej Mazurkiewicz
> Cc: pgsql-general(at)postgreSQL(dot)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(at)yellowbank(dot)com