Member Avatar for 54uydf

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 because: n/a
  • 2 Contributors
  • 1 Reply
  • 139 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by smantscheff
Member Avatar for smantscheff

What is the output of

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

?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.