CODEWITHSUNDEEP
mysql
hankey39
hankey39

Reputation: 359

Update Primary Key by updating foreign key

I have a Member table as follows:

Member Table:

 Name              |          Handicap
 Joe Bloggs        |             18

Stableford Table:

  Player_name    |     Player Handicap    |   Score   |   Handicap Change
    Joe Bloggs   |            18          |     38    |         17

I now want to update the Player Handicap with the Handicap Change value (17) but I get an error:

cannot update a child row.

Player Handicap is a FOREIGN KEY in the Stableford table. It REFERENCES member(handicap).

When I update the Player Handicap field in Stableford table I also want the handicap field in my member table to be updated. Is this possible?

Upvotes: 0

Views: 78

Answers (1)

user207421
user207421

Reputation: 310874

You don't want to do this at all.

What you have here is a classic denormalization.

It doesn't make any sense whatsoever to have Handicap as a foreign key. It only makes sense to have the player himself as a foreign key.

You need to get rid of the Handicap field from the Stableford table, and get it from the Member table via the member's name as a foreign key, via a join, when necessary.

You do not want to repeat any attribute of a Member other than his key in any other table.

Upvotes: 2

Related Questions