Обсуждение: Drop foreign keys
Hi, I created some references with foreign keys. But now I have to drop some of the references. What is the way to do this? Is there a command for this? Or have I drop the table and recreate it? Postgres gave the references names like "RI_ConstraintTrigger_494516". If there is a way to drop them one by one how do I get the information on which foreign key point these Postgres names? All references are created like this: ... constraint tPerson_FK03 foreign key (titel) references tTitel (tnr) ON UPDATE CASCADE ON DELETE RESTRICT ... Greetings, Andreas
On 8 Jan 2003, Andreas Schlegel wrote: > Hi, > > I created some references with foreign keys. But now I have to drop > some of the references. What is the way to do this? Is there a command > for this? Or have I drop the table and recreate it? > Postgres gave the references names like "RI_ConstraintTrigger_494516". > If there is a way to drop them one by one how do I get the information > on which foreign key point these Postgres names? > > All references are created like this: > ... constraint tPerson_FK03 foreign key (titel) > references tTitel (tnr) > ON UPDATE CASCADE ON DELETE RESTRICT ... What version are you using? In 7.3, you should be able to say ALTER TABLE <table> DROP CONSTRAINT tPerson_FK03; In previous versions, you'll need to manually drop the three triggers that are created (you can find the correct triggers by looking for tgconstrname='tPerson_FK03') and you'll need to double quote the tgname for DROP TRIGGER commands.
Stephan Szabo wrote: >>I created some references with foreign keys. But now I have to drop >>some of the references. What is the way to do this? Is there a command >>for this? Or have I drop the table and recreate it? >>Postgres gave the references names like "RI_ConstraintTrigger_494516". >>If there is a way to drop them one by one how do I get the information >>on which foreign key point these Postgres names? >> >> >> >What version are you using? > Sorry, I forgot: 7.2.1 >In previous versions, you'll need to manually drop the >three triggers that are created (you can find the correct >triggers by looking for tgconstrname='tPerson_FK03') and >you'll need to double quote the tgname for DROP TRIGGER >commands. > I found 3 constrains for this reference. I dropped successfully the first. But when I tried to drop the second and the third I got the message: "ERROR: DropTrigger: there is no trigger RI_ConstraintTrigger_494494 on relation tperson. " I checked the table (\d tperson) and indeed they diappeared. But I in the table pg_trigger I still see the the other, which were refused to drop! Any idea what went wrong? Greetings, Andreas
On Wed, 8 Jan 2003, Andreas Schlegel wrote: > Stephan Szabo wrote: > > >>I created some references with foreign keys. But now I have to drop > >>some of the references. What is the way to do this? Is there a command > >>for this? Or have I drop the table and recreate it? > >>Postgres gave the references names like "RI_ConstraintTrigger_494516". > >>If there is a way to drop them one by one how do I get the information > >>on which foreign key point these Postgres names? > >> > >> > >> > >What version are you using? > > > Sorry, I forgot: 7.2.1 > > >In previous versions, you'll need to manually drop the > >three triggers that are created (you can find the correct > >triggers by looking for tgconstrname='tPerson_FK03') and > >you'll need to double quote the tgname for DROP TRIGGER > >commands. > > > I found 3 constrains for this reference. I dropped successfully the > first. But when I tried to drop the second and the third I got the message: > "ERROR: DropTrigger: there is no trigger RI_ConstraintTrigger_494494 on > relation tperson. " > > I checked the table (\d tperson) and indeed they diappeared. But I in > the table pg_trigger I still see the the other, which were refused to drop! > > Any idea what went wrong? Two of the triggers actually exist on the table the reference was to (not the table making the reference). I'd forgotten to mention that in the above, sorry.
Stephan Szabo wrote: >On Wed, 8 Jan 2003, Andreas Schlegel wrote: > > >>I found 3 constrains for this reference. I dropped successfully the >>first. But when I tried to drop the second and the third I got the message: >>"ERROR: DropTrigger: there is no trigger RI_ConstraintTrigger_494494 on >>relation tperson. " >> >>I checked the table (\d tperson) and indeed they diappeared. But I in >>the table pg_trigger I still see the the other, which were refused to drop! >> >>Any idea what went wrong? >> >> > >Two of the triggers actually exist on the table the reference was to (not >the table making the reference). I'd forgotten to mention that in the >above, sorry. > That's it! Now all trigger concerning the two tables are removed. Thanks for help, Andreas