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