From: | <bmccoy(at)chapelperilous(dot)net> |
---|---|
To: | Matthew Kennedy <mkennedy(at)hssinc(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: (redefinition) alter table, add foreign key constraint |
Date: | 2000-10-16 16:13:37 |
Message-ID: | Pine.LNX.4.10.10010161206480.1423-100000@chapelperilous.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2000-10-16 16:18:15 | Re: 7.1 Release Date |
Previous Message | Jim Mercer | 2000-10-16 16:12:36 | Re: 7.1 Release Date |