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

From: mkennedy(at)hssinc(dot)com (Matthew Kennedy)
To: pgsql-general(at)postgresql(dot)org
Cc: bmccoy(at)chapelperilous(dot)net
Subject: Re: (redefinition) alter table, add foreign key constraint
Date: 2000-10-17 15:42:55
Message-ID: 39EC737F.952F59B7@opushealthcare.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

bmccoy(at)chapelperilous(dot)net wrote:
> On Tue, 17 Oct 2000, Matthew Kennedy wrote:
> > Is it possible to perform an alter table with add foreign key constraint
> > in PostgreSQL? We are trying to support multiple DBMS in our project by
> > keeping to a common, compatible denominator. Oracle, supports such a
> > construct. Does (or might) PostgreSQL support this feature?
> Yes...
> ALTER TABLE <table> ADD <constraint definition>
> Brett W. McCoy

Firstly...

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?

Secondly...

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"

I don't really need postgres to do anything with the name of the foreign
key. I would like it to ignore it, rather than bawk at it. (I'm trying
to feed the output of a relational design tool to PostgreSQL without
having to write too many filters to strip out things postgres doesn't
like). Perhaps I just have the syntax wrong?

Thanks and much appreciated,

Matt

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Eisentraut 2000-10-17 15:49:38 Re: Compiling JDBC Driver
Previous Message Tom Lane 2000-10-17 15:37:45 Re: Stumped on PlPgSql