is there a way to DROP foreign key constraint ?

Started by Hannu Krosingabout 25 years ago3 messages
#1Hannu Krosing
hannu@tm.ee

I'm unable to find the complementary function to

ALTER TABLE t ADD FOREIGN KEY(id) REFERENCES pkt(pk);

I would try DROP TRIGGER, but I've also been unable to
find a way to name the constraint ;(

the built-in help is both inadequate and inconsistent
----8<---------8<---------8<-------------8<-----8<---------8<-----
amphora2=# \h alter table
Command: ALTER TABLE
Description: Modifies table properties
Syntax:
ALTER TABLE table [ * ]
ADD [ COLUMN ] column type
ALTER TABLE table [ * ]
ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT }
ALTER TABLE table [ * ]
RENAME [ COLUMN ] column TO newcolumn
ALTER TABLE table
RENAME TO newtable
ALTER TABLE table
ADD table constraint definition

amphora2=# \h create table
Command: CREATE TABLE
Description: Creates a new table
Syntax:
CREATE [ TEMPORARY | TEMP ] TABLE table (
column type
[ NULL | NOT NULL ] [ UNIQUE ] [ DEFAULT value ]
[column_constraint_clause | PRIMARY KEY } [ ... ] ]
[, ... ]
[, PRIMARY KEY ( column [, ...] ) ]
[, CHECK ( condition ) ]
[, table_constraint_clause ]
) [ INHERITS ( inherited_table [, ...] ) ]

amphora2=# \h column_constraint_clause
No help available for 'column_constraint_clause'.
Try \h with no arguments to see available help.
amphora2=# \h column constraint definition
No help available for 'column constraint definition'.
Try \h with no arguments to see available help.
----8<---------8<---------8<-------------8<-----8<---------8<-----

#2Stephan Szabo
sszabo@megazone23.bigpanda.com
In reply to: Hannu Krosing (#1)
Re: is there a way to DROP foreign key constraint ?

On Fri, 20 Oct 2000, Hannu Krosing wrote:

I'm unable to find the complementary function to

ALTER TABLE t ADD FOREIGN KEY(id) REFERENCES pkt(pk);

Currently, ALTER TABLE ... DROP table constraint definition
doesn't exist.

I would try DROP TRIGGER, but I've also been unable to
find a way to name the constraint ;(

Umm, put the constraint name in the table constraint definition?

From the SQL spec:

<table constraint definition> ::=
[ <constraint name definition> ]
<table constraint> [ <constraint attributes> ]
<constraint name definition> ::= CONSTRAINT <constraint name>

Currently, you need to drop the three triggers from pg_trigger
that are associated with the constraint. Easiest way to find
them if you don't have a constraint name is to look at the tg_args.

#3Hannu Krosing
hannu@tm.ee
In reply to: Stephan Szabo (#2)
Re: is there a way to DROP foreign key constraint ?

Stephan Szabo wrote:

On Fri, 20 Oct 2000, Hannu Krosing wrote:

I'm unable to find the complementary function to

ALTER TABLE t ADD FOREIGN KEY(id) REFERENCES pkt(pk);

Currently, ALTER TABLE ... DROP table constraint definition
doesn't exist.

I would try DROP TRIGGER, but I've also been unable to
find a way to name the constraint ;(

Umm, put the constraint name in the table constraint definition?

From the SQL spec:

<table constraint definition> ::=
[ <constraint name definition> ]
<table constraint> [ <constraint attributes> ]
<constraint name definition> ::= CONSTRAINT <constraint name>

Thanks! I was missing the CONSTRAINT word in <constraint name
definition>

------------
Hannu