0

Hello, I think I made a mistake while connecting my tables with foreign keys :S

here's the DB-

table 1: Id, year,..... (2 Primary keys)
table 2: tbl2Id, ...

now the table that connects tbl1 and 2 with m..m relation-
table 3: Id, year,tbl2Id

I set the foregn keys for table 3 using the phpmyadmin relation view.
I first marked all 3 fields as keys and then in the relation view I set those:
Id-> FK from table1.Id ON DELETE cascade on UPDATE cascade
year-> FK from table1.year ON DELETE cascade on UPDATE cascade
tbl2id->FK from table2.Id ON DELETE cascade on UPDATE cascade

now I'm having problems with the year field.

if I have 2 records in table1:
Id _|_ year _|
_1__|_05__|
_2__|_05__|

and in table 3 I connected them to table 2 and the records are:

Id _| _year_ |_tbl2Id _|
_1__|_05__|__1__|
_2__|_05__|__1__|


so the problem is- when I UPDATE year field in record 1 in table 1 to '02'
what happens in table 3 is that all the records get 02 instead of 05.

I get this:

_Id_ |_ year _|_tbl2Id_ |
__1__|_02__|__1__|
__2__|_02__|__1__|

what I need to happen is this:
_Id _|_ year_ |_tbl2Id_ |
__1__|_02__|__1__|
__2__|_05__|__1__|

how can I fix it? I'm guessing it's a FK problem, but I may be wrong..

p.s I tried changing the ON UPDATE to different values instead of cascade but then it won't let me change records on table 1!

Edited by 54uydf: n/a

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by smantscheff
0

What is the output of

SHOW CREATE TABLE `table 1`;
SHOW CREATE TABLE `table 2`;
SHOW CREATE TABLE `table 3`;

?

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.