Hi All,
I’m trying to modify the sec_users table by changing the Primary Key from login to a existing field called Employee_ID. This is a field that was added to the original sec_users table. I made the change just fine in Dev, but now in Prod I ran into a Foreign Key Constraint. When I look at the sec_users_groups table (the likely culprit) it shows only on FK constraint on the sec_groups table using group_ID and nothing on the sec_users table.
to find out what all the FK contraints on the sec_Users.login field, I ran the following:
SELECT
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = ‘ourdatabase’ AND
REFERENCED_TABLE_NAME = ‘sec_users’ AND
REFERENCED_COLUMN_NAME = ‘login’;
it returned which is saying that Sec_users_groups table has a FK constraint on sec_users.login:
Table Name: sec_users_groups
column: login;
constraint: SEC_USERS_GROUPS_IBFK_1;
Reference table name: sec_users;
referenced column name: login
but the Problem is when I look at the table, SEC_USERS_GROUPS there is no Foreign Key Constraint by that name. There is another one that is called
sec_users_groups_ibfk_2
How do I deal with appears to be a ghost FK constraint??
Any help would be appreciated.
BTW the reason is that we need the sec_users.Employee_ID to auto_increment and MySQL requires an auto_increment field to also be the Primary key.