Re: (redefinition) alter table, add foreign key constraint

On Tue, 17 Oct 2000, Matthew Kennedy wrote:

> A little redefinition of my question is in order. If I try doing this:
>
> create table things (name varchar(20), state(2));
> create table states (abbreviation varchar(2));
> alter table things
>     add foreign key (state)
>     references states (abbreviation);
>
> Postgres responds with:
>
> NOTICE:  ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s)
> for
> FOREIGN KEY check(s)
> CREATE
>
> I am wondering what the implications of postgres using implicit triggers
> for foreign key constraints are? I guess if there were no implications
> (ie. implicit triggers were a perfect implimentation of foreign keyt
> constraints), then there would be no warning message?

I don't think of it as a warning message, just informational -- you are
creating explicit triggers to handle the referential integrity.  You get
the same messages when you create a primary key -- you get a notice that
an implicit index is being built.

> Postgres doesn't seem to take the name of a foreign key (it's a parse
> error to include it). This is slightly different to what SQL92 and
> Oracle allow. I beleive I should have been able to write:
>
> alter table things
>     add foreign key things2states (state)
>     references states (abbreviation);
>
>
> Postgres responds with:
>
> ERROR:  parser: parse error at or near "things2states"

The proper syntax is

add constraint <name> foreign key <column> references <table(column)> ...

This is consistent with how Oracle does it and how SQL92 defines it.

Brett W. McCoy
                                              http://www.chapelperilous.net
---------------------------------------------------------------------------
Save the bales!