CppNoob CppNoob - 4 months ago 24
SQL Question

Rails: creating column references without foreign keys

In Rails 4 models, I want to model an employee-department relationship. An employee belongs_to a department and a department has_many employees. Can I create a department_id reference in the employee table without making it a foreign key?

The reason I want to do this is that I suspect this impacts the record-locking behavior in MySQL, which is the database I am using, and it may be cause random errors when I perform concurrent updates from multiple threads in my Rails application.

Answer

Yes you can. If you're using Rails 4.2 and using references or add_reference within your migrations, just set the :foreign_key option to false.

You can also take the approach described by @user1063998 and just add an integer column called department_id to your employees table although in that case, I would recommend also adding an index for this column.