Hi All,
I have an advanced question on foreign keys.
I have one INNODB table called rb_userbase - it is the main table for people, their name and primary address/phone etc. Its primary key is called UserName [char(30) NOT NULL]. The primary key functions as its full index.
Another INNODB table is called rb_account - it has two fields which refer to userbase.UserName (on delete restrict, on update cascade):
1. rb_account.AcctName [char(30) NOT NULL] - each record would hold information about the account - this field is the account name.
2. rb_account.UserName [char(30) NOT NULL] - this field would hold a username value also - for a primary account owner, AcctName=UserName.
The way the system works, one account can have multiple users:
records in rb_account:
AcctName UserName
person1 person1
person1 person2
person1 person3
person2 person2
person2 person3
this means person1 has an account, and person2 has an account. Person2 and 3 are users in person1's account, and person3 is a user in person2's account.
HERE IS THE PROBLEM:
I need for admin reasons to update rb_userbase.UserName. This cascades fine to rb_account and the other tables, BUT I also have a unique key set on AcctName-UserName (since each combo should be unique). With this unique compound key in place, CASCADE DOES NOT WORK. I get this error:
#1216 - Cannot add or update a child row: a foreign key constraint fails.
When I drop the compound unique key on the two foreign key fields, cascade updates work fine. For now, a workaround would be to temporarily suspend the compound unique key, and re-insert it when the UserName cascades, but that's dangerous in my humbler opinion.
Any idea how I can cascade update successfully and still keep this rb_account.AcctName-UserName constraint in place?
Thanks,
Samuel
