Thread: 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