Thread: foreign key constraints, cannot delete
How can a delete rows from a table that has foreign key constraints? Here is how I have it set up. I have 2 tables, tableA has fields and id's and tableB has fields that reference tableA's id's. I'm not able to do this BEGIN TRANSACTION; DELETE FROM tableB WHERE tableAid = 5; DELETE FROM tableA WHERE id = 5; COMMIT TRANSATION; Even though I delete everything from tableB that references tableA, I can't delete the field from tableA. Is this because of the begin and commit? Do I need to commit after each delete? The only problem with commiting after each is, if one fails for some reason, I need it to rollback. -Josh
You havn't posted the exact error message. You'll have to if you want people to properly explain what's going on... On Fri, Oct 08, 2004 at 04:07:43PM -0500, Josh Close wrote: > How can a delete rows from a table that has foreign key constraints? > Here is how I have it set up. > > I have 2 tables, tableA has fields and id's and tableB has fields that > reference tableA's id's. I'm not able to do this > > BEGIN TRANSACTION; > > DELETE FROM tableB > WHERE tableAid = 5; > > DELETE FROM tableA > WHERE id = 5; > > COMMIT TRANSATION; > > Even though I delete everything from tableB that references tableA, I > can't delete the field from tableA. > > Is this because of the begin and commit? Do I need to commit after each delete? > > The only problem with commiting after each is, if one fails for some > reason, I need it to rollback. > > > -Josh > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
On Fri, 8 Oct 2004 23:15:24 +0200, Martijn van Oosterhout <kleptog@svana.org> wrote: > You havn't posted the exact error message. You'll have to if you want > people to properly explain what's going on... update or delete on "tblheadings" violates foreign key constraint "$1" on "tblheadings" DETAIL: Key (iid)=(22) is still referenced from table "tblheadings" -Josh
On Fri, Oct 08, 2004 at 04:21:01PM -0500, Josh Close wrote: > On Fri, 8 Oct 2004 23:15:24 +0200, Martijn van Oosterhout > <kleptog@svana.org> wrote: > > You havn't posted the exact error message. You'll have to if you want > > people to properly explain what's going on... > > update or delete on "tblheadings" violates foreign key constraint "$1" > on "tblheadings" DETAIL: Key (iid)=(22) is still referenced from table > "tblheadings" This looks like tblheadings has a foreign key reference to itself. Is this the *exact* error message, cut-and-pasted? What do your table definitions look like? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Fri, 8 Oct 2004 18:14:50 -0600, Michael Fuhr <mike@fuhr.org> wrote: > This looks like tblheadings has a foreign key reference to itself. > Is this the *exact* error message, cut-and-pasted? What do your > table definitions look like? > > -- > Michael Fuhr There isn't a foreign key reference to itself. I figured out the problem. I had to print out all the queries and manually figure out the data that was being deleted. Turns out there was one value the wasn't being removed. So the begin and commit do work like they should. Thanks. -Josh