Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
On Thu, 5 Dec 2002, Dan Langille wrote: We support alter table add foreign key. How about supporting alter table drop foreign key? - he said as he went to drop a foreign key It seems to work for me on my 7.3b2 system with alter table table drop constraint constraint name; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 8:20, Stephan Szabo wrote: On Thu, 5 Dec 2002, Dan Langille wrote: We support alter table add foreign key. How about supporting alter table drop foreign key? - he said as he went to drop a foreign key It seems to work for me on my 7.3b2 system with alter table table drop constraint constraint name; How was that FK added? How did you determine the constraint name? -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 8:20, Stephan Szabo wrote: On Thu, 5 Dec 2002, Dan Langille wrote: We support alter table add foreign key. How about supporting alter table drop foreign key? - he said as he went to drop a foreign key It seems to work for me on my 7.3b2 system with alter table table drop constraint constraint name; Premature send.. sorry How was that FK added? How did you determine the constraint name? How would you do that if the FK was added with the following syntax? alter table table add foreign key (column) references othertable (othercolumn) on update cascade on delete cascade; -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
On Thu, 5 Dec 2002, Dan Langille wrote: On 5 Dec 2002 at 8:20, Stephan Szabo wrote: On Thu, 5 Dec 2002, Dan Langille wrote: We support alter table add foreign key. How about supporting alter table drop foreign key? - he said as he went to drop a foreign key It seems to work for me on my 7.3b2 system with alter table table drop constraint constraint name; Premature send.. sorry How was that FK added? How did you determine the constraint name? alter table table add constraint name foreign key ... How would you do that if the FK was added with the following syntax? alter table table add foreign key (column) references othertable (othercolumn) on update cascade on delete cascade; IIRC, the constraint will get an automatic name of the form $n in such cases. I believe if you do a \d on the table, it gives the name in the constraint definitions (on one of mine i get: Foreign Key constraints: $1 FOREIGN KEY (a) REFERENCES qqq(a) ON UPDATE CASCADE ON DELETE NO ACTION Where $1 is the name of the constraint. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 8:44, Stephan Szabo wrote: On Thu, 5 Dec 2002, Dan Langille wrote: On 5 Dec 2002 at 8:20, Stephan Szabo wrote: On Thu, 5 Dec 2002, Dan Langille wrote: We support alter table add foreign key. How about supporting alter table drop foreign key? - he said as he went to drop a foreign key It seems to work for me on my 7.3b2 system with alter table table drop constraint constraint name; Premature send.. sorry How was that FK added? How did you determine the constraint name? alter table table add constraint name foreign key ... How would you do that if the FK was added with the following syntax? alter table table add foreign key (column) references othertable (othercolumn) on update cascade on delete cascade; IIRC, the constraint will get an automatic name of the form $n in such cases. I believe if you do a \d on the table, it gives the name in the constraint definitions (on one of mine i get: Foreign Key constraints: $1 FOREIGN KEY (a) REFERENCES qqq(a) ON UPDATE CASCADE ON DELETE NO ACTION Where $1 is the name of the constraint. Thanks. In my 7.2.3 database, the table in question has: Primary key: watch_list_staging_pkey Check constraints: watch_list_stag_from_watch_list ((from_watch_list = 't'::bool) OR (from_watch_list = 'f'::bool)) watch_list_stagin_from_pkg_info ((from_pkg_info = 't'::bool) OR (from_pkg_info = 'f'::bool)) Triggers: RI_ConstraintTrigger_4278482, RI_ConstraintTrigger_4278488 No mention of FK constraints. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 11:47, Dan Langille wrote: On 5 Dec 2002 at 8:44, Stephan Szabo wrote: On Thu, 5 Dec 2002, Dan Langille wrote: On 5 Dec 2002 at 8:20, Stephan Szabo wrote: On Thu, 5 Dec 2002, Dan Langille wrote: We support alter table add foreign key. How about supporting alter table drop foreign key? - he said as he went to drop a foreign key It seems to work for me on my 7.3b2 system with alter table table drop constraint constraint name; Premature send.. sorry How was that FK added? How did you determine the constraint name? alter table table add constraint name foreign key ... How would you do that if the FK was added with the following syntax? alter table table add foreign key (column) references othertable (othercolumn) on update cascade on delete cascade; IIRC, the constraint will get an automatic name of the form $n in such cases. I believe if you do a \d on the table, it gives the name in the constraint definitions (on one of mine i get: Foreign Key constraints: $1 FOREIGN KEY (a) REFERENCES qqq(a) ON UPDATE CASCADE ON DELETE NO ACTION Where $1 is the name of the constraint. Thanks. In my 7.2.3 database, the table in question has: Primary key: watch_list_staging_pkey Check constraints: watch_list_stag_from_watch_list ((from_watch_list = 't'::bool) OR (from_watch_list = 'f'::bool)) watch_list_stagin_from_pkg_info ((from_pkg_info = 't'::bool) OR (from_pkg_info = 'f'::bool)) Triggers: RI_ConstraintTrigger_4278482, RI_ConstraintTrigger_4278488 No mention of FK constraints. Found the solution: drop trigger RI_ConstraintTrigger_4278488 on watch_list_staging; Given that the FK in question did not have a name to start with, I concede that it would be difficult to code DROP FOREIGN KEY. What about supporting ALTER TABLE table ADD FOREIGN KEY keyname ... which at present we don't? That would then make dropping the FK a simple coding issue? -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
On Thu, 5 Dec 2002, Dan Langille wrote: Found the solution: drop trigger RI_ConstraintTrigger_4278488 on watch_list_staging; Actually there are three triggers for the constraint. You may have dangling triggers on the other table of the constraint. It's one on the table the constraint's defined on and two on the referenced table. Given that the FK in question did not have a name to start with, I concede that it would be difficult to code DROP FOREIGN KEY. What about supporting ALTER TABLE table ADD FOREIGN KEY keyname ... which at present we don't? That would then make dropping the FK a simple coding issue? ISTM, that's ALTER TABLE table ADD CONSTRAINT name FOREIGN KEY ... which should be there in any 7.x. And the drop constraint for foreign keys (and the \d display stuff) is new in 7.3. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 9:02, Stephan Szabo wrote: On Thu, 5 Dec 2002, Dan Langille wrote: Found the solution: drop trigger RI_ConstraintTrigger_4278488 on watch_list_staging; Actually there are three triggers for the constraint. You may have dangling triggers on the other table of the constraint. It's one on the table the constraint's defined on and two on the referenced table. Given that the FK in question did not have a name to start with, I concede that it would be difficult to code DROP FOREIGN KEY. What about supporting ALTER TABLE table ADD FOREIGN KEY keyname ... which at present we don't? That would then make dropping the FK a simple coding issue? ISTM, that's ALTER TABLE table ADD CONSTRAINT name FOREIGN KEY ... which should be there in any 7.x. Agreed. But the syntax is different. If we are supporting ALTER TABLE table ADD FOREIGN KEY without a name, why not support it with a name? And the drop constraint for foreign keys (and the \d display stuff) is new in 7.3. That's going to be much more useful. I installed 7.3 for testing this morning. Looking at it now, I no longer see a need for a DROP FOREIGN KEY. Thank you. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
On Thu, 5 Dec 2002, Dan Langille wrote: On 5 Dec 2002 at 9:02, Stephan Szabo wrote: On Thu, 5 Dec 2002, Dan Langille wrote: Found the solution: drop trigger RI_ConstraintTrigger_4278488 on watch_list_staging; Actually there are three triggers for the constraint. You may have dangling triggers on the other table of the constraint. It's one on the table the constraint's defined on and two on the referenced table. Given that the FK in question did not have a name to start with, I concede that it would be difficult to code DROP FOREIGN KEY. What about supporting ALTER TABLE table ADD FOREIGN KEY keyname ... which at present we don't? That would then make dropping the FK a simple coding issue? ISTM, that's ALTER TABLE table ADD CONSTRAINT name FOREIGN KEY ... which should be there in any 7.x. Agreed. But the syntax is different. If we are supporting ALTER TABLE table ADD FOREIGN KEY without a name, why not support it with a name? When we talk about ALTER TABLE ADD FOREIGN KEY we're being imprecise, so I think that might be why we're talking past each other here. Technically the syntax in question is: ALTER TABLE table ADD table constraint definition where CONSTRAINT name is an optional leading clause in a table constraint definition. ADD FOREIGN KEY is a shorthand for a foreign key constraint (technically unnamed). Thus you can also say things like: ALTER TABLE table ADD CONSTRAINT blah CHECK (foo!=0); to make a named check constraint. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 9:31, Stephan Szabo wrote: When we talk about ALTER TABLE ADD FOREIGN KEY we're being imprecise, so I think that might be why we're talking past each other here. Technically the syntax in question is: ALTER TABLE table ADD table constraint definition where CONSTRAINT name is an optional leading clause in a table constraint definition. ADD FOREIGN KEY is a shorthand for a foreign key constraint (technically unnamed). Understood. What about allowing a named foreign key? I haven't checked the RFCs Thus you can also say things like: ALTER TABLE table ADD CONSTRAINT blah CHECK (foo!=0); to make a named check constraint. Understood. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
On Thu, 5 Dec 2002, Dan Langille wrote: On 5 Dec 2002 at 9:31, Stephan Szabo wrote: When we talk about ALTER TABLE ADD FOREIGN KEY we're being imprecise, so I think that might be why we're talking past each other here. Technically the syntax in question is: ALTER TABLE table ADD table constraint definition where CONSTRAINT name is an optional leading clause in a table constraint definition. ADD FOREIGN KEY is a shorthand for a foreign key constraint (technically unnamed). Understood. What about allowing a named foreign key? I haven't checked the RFCs Here's a part of what SQL92 (draft) has to say about table constraint definitions: table constraint definition ::= [ constraint name definition ] table constraint [ constraint attributes ] table constraint ::= unique constraint definition | referential constraint definition | check constraint definition constraint name definition ::= CONSTRAINT constraint name referential constraint definition ::= FOREIGN KEY left paren referencing columns right paren references specification 11.6 Syntax Rules 2) If constraint name definition is not specified, then a con- straint name definition that contains an implementation- dependent constraint name is implicit. The assigned con- straint name shall obey the Syntax Rules of an explicit con- straint name. In our case, the implementation dependent naming scheme is I believe $n where n is the maximum one already there for that table +1 I would guess. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 9:51, Stephan Szabo wrote: On Thu, 5 Dec 2002, Dan Langille wrote: On 5 Dec 2002 at 9:31, Stephan Szabo wrote: When we talk about ALTER TABLE ADD FOREIGN KEY we're being imprecise, so I think that might be why we're talking past each other here. Technically the syntax in question is: ALTER TABLE table ADD table constraint definition where CONSTRAINT name is an optional leading clause in a table constraint definition. ADD FOREIGN KEY is a shorthand for a foreign key constraint (technically unnamed). Understood. What about allowing a named foreign key? I haven't checked the RFCs Here's a part of what SQL92 (draft) has to say about table constraint definitions: table constraint definition ::= [ constraint name definition ] table constraint [ constraint attributes ] table constraint ::= unique constraint definition | referential constraint definition | check constraint definition constraint name definition ::= CONSTRAINT constraint name referential constraint definition ::= FOREIGN KEY left paren referencing columns right paren references specification 11.6 Syntax Rules 2) If constraint name definition is not specified, then a con- straint name definition that contains an implementation- dependent constraint name is implicit. The assigned con- straint name shall obey the Syntax Rules of an explicit con- straint name. In our case, the implementation dependent naming scheme is I believe $n where n is the maximum one already there for that table +1 I would guess. Thanks. I guess I should rename my thread to 7.4 - TODO : allow constraint names when using the ALTER TABLE table ADD FOREIGN KEY syntax. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
Thanks. I guess I should rename my thread to 7.4 - TODO : allow constraint names when using the ALTER TABLE table ADD FOREIGN KEY syntax. You can do that now. ALTER TABLE table ADD CONSTRAINT const FOREIGN KEY -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
Dan Langille wrote: On 5 Dec 2002 at 11:47, Dan Langille wrote: Primary key: watch_list_staging_pkey Check constraints: watch_list_stag_from_watch_list ((from_watch_list = 't'::bool) OR (from_watch_list = 'f'::bool)) watch_list_stagin_from_pkg_info ((from_pkg_info = 't'::bool) OR (from_pkg_info = 'f'::bool)) Triggers: RI_ConstraintTrigger_4278482, RI_ConstraintTrigger_4278488 No mention of FK constraints. Found the solution: drop trigger RI_ConstraintTrigger_4278488 on watch_list_staging; You should now go to the table this RI constraint was referring to and delete the two triggers in there as well. They will still be checking for deletions and updates. Look for something like RI_ConstraintTrigger_4278490 RI_ConstraintTrigger_4278492 and with the associated procedure RI_FKey_noaction_del and RI_FKey_noaction_upd BTW, the rhdb-admin program can drop the constraints for you, even the unnamed ones on backends 7.2 up. You can download it from: http://sources.redhat.com/rhdb Of course, now that you broke the set of triggers for this FK constraint you'll still need to drop the other ones by hand. But the tool at least will show you the column and table involved so it will be easier to identify the two you have to get rid of. Regards, Fernando -- Fernando Nasser Red Hat - Toronto E-Mail: [EMAIL PROTECTED] 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 14:17, Fernando Nasser wrote: Dan Langille wrote: On 5 Dec 2002 at 11:47, Dan Langille wrote: drop trigger RI_ConstraintTrigger_4278488 on watch_list_staging; You should now go to the table this RI constraint was referring to and delete the two triggers in there as well. They will still be checking for deletions and updates. Look for something like RI_ConstraintTrigger_4278490 RI_ConstraintTrigger_4278492 and with the associated procedure RI_FKey_noaction_del and RI_FKey_noaction_upd Oh thank you! I didn't know about those. FWIW, I've just documented this exercise at http://www.freebsddiary.org/postgresql-dropping- constraints.php so corrections are most welcome. BTW, the rhdb-admin program can drop the constraints for you, even the unnamed ones on backends 7.2 up. You can download it from: http://sources.redhat.com/rhdb Thanks. I hope to check that out one day. Of course, now that you broke the set of triggers for this FK constraint you'll still need to drop the other ones by hand. But the tool at least will show you the column and table involved so it will be easier to identify the two you have to get rid of. I did the identification by hand and fixed it up that way. Hopefully there's nothing else in there I've done wrong. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
Thanks. I guess I should rename my thread to 7.4 - TODO : allow constraint names when using the ALTER TABLE table ADD FOREIGN KEY syntax. You can do that now. ALTER TABLE table ADD CONSTRAINT const FOREIGN KEY That I know. That syntax is radically different from that proposed. Isn't it identical? The CONSTRAINT const is SQL standard optional clause for all commands that add constraints. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 11:52, Christopher Kings-Lynne wrote: Thanks. I guess I should rename my thread to 7.4 - TODO : allow constraint names when using the ALTER TABLE table ADD FOREIGN KEY syntax. You can do that now. ALTER TABLE table ADD CONSTRAINT const FOREIGN KEY That I know. That syntax is radically different from that proposed. I take back the adjective radical Isn't it identical? The CONSTRAINT const is SQL standard optional clause for all commands that add constraints. Except that one is ADD CONSTRAINT, the other is an ADD FOREIGN KEY. They are similar in nature but different overall. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
Isn't it identical? The CONSTRAINT const is SQL standard optional clause for all commands that add constraints. Except that one is ADD CONSTRAINT, the other is an ADD FOREIGN KEY. They are similar in nature but different overall. I think you're getting a little confused here, Dan. http://www3.us.postgresql.org/users-lounge/docs/7.3/postgres/sql-altertable. html There is only one command for adding constraints to a table. It has this syntax: ALTER TABLE [ ONLY ] table [ * ] ADD table_constraint The table_constraint clause is defined like this (basically): [CONSTRAINT blah] (PRIMARY KEY or UNIQUE or FOREIGN KEY) ... So, the CONSTRAINT blah clause allows you to specify a name for any of the 3 types of constraint: primary key, unique or foreign key. There's nothing special about foreign keys in this case. If you don't put in the CONSTRAINT blah clause, you get an automatically assigned constraint name. Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
On Thu, 2002-12-05 at 14:52, Christopher Kings-Lynne wrote: Thanks. I guess I should rename my thread to 7.4 - TODO : allow constraint names when using the ALTER TABLE table ADD FOREIGN KEY syntax. You can do that now. ALTER TABLE table ADD CONSTRAINT const FOREIGN KEY That I know. That syntax is radically different from that proposed. Isn't it identical? The CONSTRAINT const is SQL standard optional clause for all commands that add constraints. Not to mention the same as the CREATE TABLE syntax for constraints that we already have. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 12:09, Christopher Kings-Lynne wrote: Isn't it identical? The CONSTRAINT const is SQL standard optional clause for all commands that add constraints. Except that one is ADD CONSTRAINT, the other is an ADD FOREIGN KEY. They are similar in nature but different overall. I think you're getting a little confused here, Dan. http://www3.us.postgresql.org/users-lounge/docs/7.3/postgres/sql-altertable. html There is only one command for adding constraints to a table. It has this syntax: ALTER TABLE [ ONLY ] table [ * ] ADD table_constraint The table_constraint clause is defined like this (basically): [CONSTRAINT blah] (PRIMARY KEY or UNIQUE or FOREIGN KEY) ... So, the CONSTRAINT blah clause allows you to specify a name for any of the 3 types of constraint: primary key, unique or foreign key. There's nothing special about foreign keys in this case. If you don't put in the CONSTRAINT blah clause, you get an automatically assigned constraint name. Regardless of what is documented, the following is valid and works: ALTER TABLE slave ADD FOREIGN KEY (master_id) REFERENCES master (id) ON DELETE CASCADE; -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
Dan Langille [EMAIL PROTECTED] writes: You can do that now. ALTER TABLE table ADD CONSTRAINT const FOREIGN KEY That I know. That syntax is radically different from that proposed. So you're proposing we replace a SQL-spec-compliant syntax with one that is not? Why? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 15:36, Tom Lane wrote: Dan Langille [EMAIL PROTECTED] writes: You can do that now. ALTER TABLE table ADD CONSTRAINT const FOREIGN KEY That I know. That syntax is radically different from that proposed. So you're proposing we replace a SQL-spec-compliant syntax with one that is not? Why? If it's not compliant, I withdraw. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
