August 19th, 2013, 06:40 PM
Altering an existing database to use foreign keys
I inherited a faulty database design for a company's CRM web application that is using mysql. I want to add a foreign key to link the customer_interactions and employees tables so the customer_interactions.created_by is linked to the employees.employee_id field. the problem is that there are some values in the customer interactions table that aren't in the employees table (deleted ex-employees, among other reasons) and mysql keeps giving me an error when I try to add it.
Something I think would work (in my mind) is to not require them to always match/allow nulls, but I'm not sure if that's possible or advisable.
Could someone please point me in the right direction?
August 19th, 2013, 06:49 PM
allowing null in a foreign key is a good solution, except that for non-existent employees, you would have to lose the old (no longer valid) number
August 20th, 2013, 11:24 AM
What do you mean by lose the old number?
Originally Posted by r937
Also, I forgot to mention that the employee_id in the employees table is the primary key, so it doesn't allow nulls. I can make the created_by either allow or not allow nulls, but if I make them both not null, doesn't the foreign key not allow nulls?
August 20th, 2013, 11:41 AM
in order for the customer_interactions table to have a foreign key to the employees table, every value of employee id in the customer_interactions table must exist in the employees table
Originally Posted by jhughes
therefore, for those values of the employee id in the customer_interactions table that do not exist in the employees table, you have to obliterate them
making them NULL is the best way