Re: trigger/for key help - Mailing list pgsql-sql

On Sun, 2004-04-11 at 21:27, Tom Lane wrote:
> Bret Hughes <bhughes@elevating.com> writes:
> > FWIW I tried to use alter table but could never get the parser to accept
> > $1 as a constraint name.  I used single and double quotes as well as a
> > lame attempt \$1.
> 
> Hm, "$1" works for me ...
> 
> regression=# create table foo (f1 int primary key);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
> CREATE TABLE
> regression=# create table bar (f1 int references foo);
> CREATE TABLE
> regression=# \d bar
>       Table "public.bar"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  f1     | integer |
> Foreign-key constraints:
>     "$1" FOREIGN KEY (f1) REFERENCES foo(f1)
>  
> regression=# alter table bar drop constraint "$1";
> ALTER TABLE
> regression=#
> 
>             regards, tom lane

Here is a recreation of what I did:

elevating=# create table foo (f1 int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
elevating=# create table bar (f1 int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"bar_pkey" for table "bar"
CREATE TABLE
elevating=# ALTER TABLE ONLY foo   ADD CONSTRAINT "$1" FOREIGN KEY (f1)
REFERENCES bar;
ALTER TABLE

elevating=# \d foo        Table "foo"Column |  Type   | Modifiers 
--------+---------+-----------f1     | integer | not null
Primary key: foo_pkey
Triggers: RI_ConstraintTrigger_2042118

elevating=# \d bar        Table "bar"Column |  Type   | Modifiers 
--------+---------+-----------f1     | integer | not null
Primary key: bar_pkey
Triggers: RI_ConstraintTrelevating=# create table foo (f1 int primary
key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
elevating=# create table bar (f1 int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"bar_pkey" for table "bar"
CREATE TABLE
elevating=# ALTER TABLE ONLY foo   ADD CONSTRAINT "$1" FOREIGN KEY (f1)
REFERENCES bar;
ALTER TABLE
igger_2042119,         RI_ConstraintTrigger_2042120

elevating=# 

Not much help to me.  It was at this point I started reading old emails
and trying to get rid of the foreign key that I guessed was buried in
the trigger names somewhere.

As you say, I must not have had the syntax right since:

elevating=# alter table foo drop constraint "$1";
ALTER TABLE
elevating=# drop table foo;
DROP TABLE
elevating=# drop table bar;
DROP TABLE


Well I guess I am brain dead. this is from the psql history:
alter table only diag_logs drop contraint "$2";
alter table only diag_logs drop contraint '$2';
alter table only diag_logs drop contraint ;
alter table only diag_logs drop contraint '\$2';
alter table only diag_logs drop contraint $2;
alter table only diag_logs drop contraint $2;
alter table only diag_logs drop contraint \$2;
alter table only diag_logs drop contraint "\$2";
alter table only diag_logs drop contraint '*';
alter table only diag_logs drop contraint "$2";
alter table diag_logs drop contraint "$2";


I told you I tried all combos I could think of.  All, that is except for
spelling constraint correctly :(

Anyway, I appreciate the help.


Bret