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

Wird behavour of foreign keys

P: n/a
Hi list

As I'm new to this list please kick me in the right direction if this question
is better asked somewhere else.

I'm seing some wird behavoure regarding foreign keys.

My situation is this:
I have 3 tables:

pages
PK: page_id

page_group
pk: page_group_id
fk: pages(page_id) ON DELETE CASCADE, ON UPDATE NO_ACTION
fk: groups(group_id) ON DELETE CASCADE, ON UPDATE NO_ACTION

groups
pk: group_id

let's say I have the following data:

pages: page_group: groups:
page_id pg_id | page_id | group_id group_id
1 1 | 1 | 1 1
2 2 | 1 | 2 2

(I know this could be done more easy and the page_group table could be dropped,
but I did not make the design, I'm just struggling with it)

Now, if I move a group (say with id 1) from one page (page 1) to another (page
2)I do it by;
UPDATE page_group set page_id=2 WHERE page_group_id=1;

Now I have:

pages: page_group: groups:
page_id pg_id | page_id | group_id group_id
1 1 | 2 | 1 1
2 2 | 1 | 2 2

Now I have moved group 1 from page 1 to page 2. BUT if I delete page 1 then
page_group 1 and 2 are both delted, and so are the two groups.

This is not what I would expect, as I have moved group 1 to page 2.

Is this a BUG, or is it something that I'm doing wrong.
As i understand the ON UPDATE constraint on the foreignkey, it will only update
the foreign key if the value which it references is changed, which is not what
I'm doing.

Hope someone can help me, or tell me if it's a bug or not. Because I'm going
crazy here.

René Jensen

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Sorry, I cannot reproduce that behaviour. I guess there are more
triggers or rules in the schema than you are currently aware of.
Especially the deleting from groups is very suspicious.

what does \d for all these tables tell?
Jan

lu******@tbkol.dk wrote:
Hi list

As I'm new to this list please kick me in the right direction if this question
is better asked somewhere else.

I'm seing some wird behavoure regarding foreign keys.

My situation is this:
I have 3 tables:

pages
PK: page_id

page_group
pk: page_group_id
fk: pages(page_id) ON DELETE CASCADE, ON UPDATE NO_ACTION
fk: groups(group_id) ON DELETE CASCADE, ON UPDATE NO_ACTION

groups
pk: group_id

let's say I have the following data:

pages: page_group: groups:
page_id pg_id | page_id | group_id group_id
1 1 | 1 | 1 1
2 2 | 1 | 2 2

(I know this could be done more easy and the page_group table could be dropped,
but I did not make the design, I'm just struggling with it)

Now, if I move a group (say with id 1) from one page (page 1) to another (page
2)I do it by;
UPDATE page_group set page_id=2 WHERE page_group_id=1;

Now I have:

pages: page_group: groups:
page_id pg_id | page_id | group_id group_id
1 1 | 2 | 1 1
2 2 | 1 | 2 2

Now I have moved group 1 from page 1 to page 2. BUT if I delete page 1 then
page_group 1 and 2 are both delted, and so are the two groups.

This is not what I would expect, as I have moved group 1 to page 2.

Is this a BUG, or is it something that I'm doing wrong.
As i understand the ON UPDATE constraint on the foreignkey, it will only update
the foreign key if the value which it references is changed, which is not what
I'm doing.

Hope someone can help me, or tell me if it's a bug or not. Because I'm going
crazy here.

Rene' Jensen

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

--
#================================================= =====================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================= = Ja******@Yahoo.com #
---------------------------(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 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.