By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,710 Members | 1,960 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,710 IT Pros & Developers. It's quick & easy.

foreign key constraints, cannot delete

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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 <kl*****@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.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBZwNsY5Twig3Ge+YRAtTTAKDBgeyYUB+jn+p7iP171N 6l0UUm1ACgiV50
In1BKxwwwIAUdx35t3nX9lg=
=PUl9
-----END PGP SIGNATURE-----

Nov 23 '05 #2

P: n/a
On Fri, 8 Oct 2004 23:15:24 +0200, Martijn van Oosterhout
<kl*****@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #3

P: n/a
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
<kl*****@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/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #4

P: n/a
On Fri, 8 Oct 2004 18:14:50 -0600, Michael Fuhr <mi**@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.