Foreign Key Dependency Loop

[davestewart]

Hi there,
I'm a very experienced programmer, but quite new to MySQL and databases, and I'm learing all the time as I go along. Anyway, I'm beginning to start using a few tables, normalizing my data, and things are going well.

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

[coopster]

Welcome to WebmasterWorld, davestewart.

It depends on how you have your FOREIGN KEY Constraints [dev.mysql.com] defined.

[davestewart]

Are there any other answers?

[edited by: txbakers at 4:20 am (utc) on Mar. 1, 2007]
[edit reason] syntax change [/edit]

[txbakers]

Hi Dave. Generally, I don't use Foreign Keys, but it would seem to me that there has to be a better way around the problem rather than removing the restraint, then putting it back.

Can you wrap the issue in more than one query rather than relying on the Foreign Key?

[davestewart]

For the meantime, I've restricted myself to a one-way foreign key, as I just can't seem to do it! It's a chicken and egg situation... if I create a key on one table, it doesn't yet exist in the other, so stops halfway through.

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...

[Lilliabeth]

I'm not 100% sure I understand your structure, but I am going to suggest you consider removing AdminID from the Company table and instead place a field in the User table that will identify a user as admin or not.

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.

[davestewart]

Hi Lilliabeth,
Thanks for your input.

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