Re: alter table, add foreign key constraint

bmccoy@chapelperilous.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

On Tue, 17 Oct 2000, Matthew Kennedy wrote:

bmccoy@chapelperilous.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?

Actually, it's just to let you know. Similar to the creating unique index
messages that are(were) in for unique/primary key.

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);

I'm pretty sure the correct SQL92 syntax is:
alter table things
add constraint things2states foreign key (state)
references states (abbreviation);