#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    2
    Rep Power
    0

    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?

    Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    2
    Rep Power
    0
    Originally Posted by r937
    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
    What do you mean by lose the old number?

    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?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    Originally Posted by jhughes
    What do you mean by lose the old number?
    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

    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo