Re: [GENERAL] (redefinition) alter table, add foreign key constraint
[EMAIL PROTECTED] 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
Re: [GENERAL] (redefinition) alter table, add foreign key constraint
On Tue, 17 Oct 2000, Matthew Kennedy wrote: [EMAIL PROTECTED] 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);
Re: [GENERAL] (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!
