Обсуждение: Fw: Removing foreign key and adding sequence

 > Chad,
 >
 > > alter table lists rename to lists_bak;
 > >
 > > select * from lists_bak into lists;
 > >
 > > I can add the constraints and foreign key's back in that ARE necessary,
 my
 > > only worry (and mabey you can think of another worry) is that my
 sequence
 > > is no longer attached to the id field of my table.
 > >
 > > Is this the right approach, and if so, how do I add my sequence back
in?
 >
 > Simple:  when you re-create Lists, just make sure that the id is "DEFAULT
 > NEXTVAL('sequence_name')".  That's it.  Sequences are easy.

The id is a foreign key on other tables, so I dont want to reorder the
table.  You're saying that I wouldn't reorder if I did it this way?

 >
 > However, I think it might be easier just to drop the FKs.  What version
 are
 > you using?
 >


 7.32.  Can I just drop the foreign key?  That would be SO much easier :-)

Thanks
Chad

Am Don, 2003-06-26 um 19.28 schrieb Chad Thompson:

>  7.32.  Can I just drop the foreign key?  That would be SO much easier :-)

Since 7.3 it's very easy, but even with 7.2 you could:

7.3:
\d table_name
if the key is labeled with e.g. $1:
ALTER TABLE table_name DROP CONSTRAINT "$1";

7.2:
DROP TRIGGER "RI_ConstraintTrigger_123456" on table_name;

You have to do this 3 times, because a FK in < 7.3 where just 3
constraint triggers.

you can get the trigger names with
\d table_name
or
pg_dump db_name and searching the correct CONSTRAINT TRIGGER

HTH
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de

> >  7.32.  Can I just drop the foreign key?  That would be SO much easier
:-)
>
> Since 7.3 it's very easy, but even with 7.2 you could:
>
> 7.3:
> \d table_name
> if the key is labeled with e.g. $1:
> ALTER TABLE table_name DROP CONSTRAINT "$1";
> >

Hrm... What could I be doing wrong?

db=# \d lists
                                    Table "public.lists"
      Column      |         Type          |                    Modifiers

------------------+-----------------------+---------------------------------
-----------------
 id               | bigint                | not null default
nextval('"lists_id_seq"'::text)
 first_name       | character varying(50) |
 middle_initial   | character(1)          |
 last_name        | character varying(30) |

[SNIP]

         lol_id_idx btree (list_of_lists_id)
Triggers: RI_ConstraintTrigger_10337839,
          RI_ConstraintTrigger_10337842,
          RI_ConstraintTrigger_10337860

db=# alter table lists drop constraint "RI_ConstraintTrigger_10337839";
ERROR:  ALTER TABLE / DROP CONSTRAINT: RI_ConstraintTrigger_10337839 does
not exist
db=# alter table lists drop constraint RI_ConstraintTrigger_10337839;
ERROR:  ALTER TABLE / DROP CONSTRAINT: ri_constrainttrigger_10337839 does
not exist

Am Don, 2003-06-26 um 23.19 schrieb Chad Thompson:
> > >  7.32.  Can I just drop the foreign key?  That would be SO much easier
> :-)
> >
> > Since 7.3 it's very easy, but even with 7.2 you could:
> >
> > 7.3:
> > \d table_name
> > if the key is labeled with e.g. $1:
> > ALTER TABLE table_name DROP CONSTRAINT "$1";
> > >
>
> Hrm... What could I be doing wrong?

Your FK are 7.2 style (actually they are < 7.3)
(they are triggers)

> Triggers: RI_ConstraintTrigger_10337839,
>           RI_ConstraintTrigger_10337842,
>           RI_ConstraintTrigger_10337860

Just take the example from 7.2

HTH
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de

 drop trigger "RI_ConstraintTrigger_10337860" on lists;

worked perfectly! Thanks

How come I have to do the 7.2 method when I have 7.3?

select version();
                                                 version
----------------------------------------------------------------------------
-----------------------------
 PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
20020903 (Red Hat Linux 8.0 3.2-7)
(1 row)

Thanks
Chad

----- Original Message -----
From: "Nabil Sayegh" <postgresql@e-trolley.de>
To: "Chad Thompson" <chad@weblinkservices.com>
Cc: "pgsql-novice" <pgsql-novice@postgresql.org>
Sent: Thursday, June 26, 2003 3:27 PM
Subject: Re: Fw: [NOVICE] Removing foreign key and adding sequence


> Am Don, 2003-06-26 um 23.19 schrieb Chad Thompson:
> > > >  7.32.  Can I just drop the foreign key?  That would be SO much
easier
> > :-)
> > >
> > > Since 7.3 it's very easy, but even with 7.2 you could:
> > >
> > > 7.3:
> > > \d table_name
> > > if the key is labeled with e.g. $1:
> > > ALTER TABLE table_name DROP CONSTRAINT "$1";
> > > >
> >
> > Hrm... What could I be doing wrong?
>
> Your FK are 7.2 style (actually they are < 7.3)
> (they are triggers)
>
> > Triggers: RI_ConstraintTrigger_10337839,
> >           RI_ConstraintTrigger_10337842,
> >           RI_ConstraintTrigger_10337860
>
> Just take the example from 7.2
>
> HTH
> --
>  e-Trolley Sayegh & John, Nabil Sayegh
>  Tel.: 0700 etrolley /// 0700 38765539
>  Fax.: +49 69 8299381-8
>  PGP : http://www.e-trolley.de
>
>

Am Fre, 2003-06-27 um 17.30 schrieb Chad Thompson:
>  drop trigger "RI_ConstraintTrigger_10337860" on lists;
>
> worked perfectly! Thanks
>
> How come I have to do the 7.2 method when I have 7.3?

Your db has initially been created with < 7.3 and was migrated to 7.3.
Because it't not a trivial task to automatically convert the triggers to
'real' foreign keys, you still have them as triggers.

bye
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de