Forum Moderators: open
However, I have a question about depencies between tables, using foreign keys (which I am new to).
In my application I have 2 tables that rely on each other:
- `companies`
- `users`
The following suppositions are true:
- A company can have any number of users
- A user can belong to only one company (foreign key user -> company)
- A company can have only one `admin` user (foreign key company -> user)
The issue lies with the `company.admin_id` and the `user.user_id` pairing. For example, if I want to change the `user_id` of the user who is currently an admin (by way of the foreign key `company.admin_id`) I get an error like so:
#1217 - Cannot delete or update a parent row: a foreign key constraint fails
So my question is: is there a command to have this pairing update automatically when one changes, without bugging out? If not I thought my choices may be to:
1 - Set the `company.admin_id` to NULL, change the `user.user_id`, then reset the `company.admin_id`.
2 - Remove the FOREIGN KEY constraint, make my changes, then add it again.
Can anyone with more experience chime in and tell me if I'm thinking about things the right way, and let me know how they would handle this kind of situation?
Many Thanks,
Dave
It depends on how you have your FOREIGN KEY Constraints [dev.mysql.com] defined.
[edited by: txbakers at 4:20 am (utc) on Mar. 1, 2007]
[edit reason] syntax change [/edit]
Can you wrap the issue in more than one query rather than relying on the Foreign Key?
In fact this whole foreign key thing is a bit of a pain - MySQL just throws errors the whole time. Hopefully it will provide some clues as to how best structure my PHP coding.
I discovered an ON UPDATE CASCADE clause in foreign key creation, but I can't seem to get that to work either.
My app won't fall over without all this stuff, but as a programmer I want to understand it and use it to best effect.
Hmm...
The challenge comes in making sure you don't allow multiple admins at a single company, but you should be able to work through that.
If I have misunderstood the question, I apologize.
Either would work, but with your option I may get the dual admin issue. If I was to look at the problem a different way, dual admins may be beneficial! No admins WOULD be a problem though, which I think is why I'll stick with option 1 for the time being. That way, MySQL will complain if I try to delete a user who is currently an admin.
Thanks again,
Dave
