6

I have a table with id|patient_id|client_id|active. A record is unique by patient_id, client_id meaning there should only be one enrollment per patient per client. Normally I would make that the primary key, but in rails I have id as my primary key.

What is the best way to enforce this? Validations?

Chris Muench
  • 17,444
  • 70
  • 209
  • 362

3 Answers3

8

Sounds like you have a model relationship of:

class Client < ActiveRecord::Base
  has_many :patients, :through => :enrollments
  has_many :enrollments
end

class ClientPatient < ActiveRecord::Base
  belongs_to :client
  belongs_to :patient
end

class Patient < ActiveRecord::Base
  has_many :clients, :through => :enrollments
  has_many :enrollments
end

To enforce your constraint I would do it in ActiveRecord, so that you get proper feedback when attempting to save a record that breaks the constraint. I would just modify your ClientPatient model like so:

class Enrollment < ActiveRecord::Base
  belongs_to :client
  belongs_to :patient
  validates_uniqueness_of :patient_id, :scope => :client_id
end

Be careful though because, while this is great for small-scale applications it is still prone to possible race conditions as described here: http://apidock.com/rails/v3.0.5/ActiveRecord/Validations/ClassMethods/validates_uniqueness_of under "Concurrency and Integrity"

As they describe there, you should also add a unique index to the table in the database. This will provide two immediate benefits:

  • The validation check and any searches through this model based on these two id's will perform faster (since they're indexed)
  • The uniqueness constraint will be enforced DB-side, and on the rare occurrence of a race condition you won't get bad data saved to the database... although users will get a 500 Server Error if you don't catch the error.

In a migration file add the following:

add_index :enrollments, [:patient_id, :client_id], :unique => true

Hopefully this was helpful :)

Edit (fixed some naming issues and a couple obvious bugs):

It's then very easy to find the data you're looking for:

Client.find_by_name("Bob Smith").patients
Patient.find_by_name("Henry Person").clients
nzifnab
  • 15,876
  • 3
  • 50
  • 65
  • Does :scope pick the column(s) where the it must be unique within? For example by saying validates_uniqueness_of :patient_id, :scope => :client_id. Does this translate to "Patient ID must be unique per client_id" – Chris Muench Mar 08 '11 at 00:41
  • Yes that is what the :scope option does. It's essentially the same thing as enforcing a composite key constraint though isn't it? You can have two client ID's that are the same, just as long as their patient ID is different. And on the reverse you can have two patient ID's that are the same, but the client ID's cannot match. – nzifnab Mar 08 '11 at 03:24
  • 1
    Is: validates_uniqueness_of :patient_id, :scope => :client_id and validates_uniqueness_of :client_id, :scope => :patient_id the same? – Chris Muench Mar 08 '11 at 04:01
  • 1
    I believe so. You can tail your development.log file to see the query that gets run in either instance to verify. – nzifnab Mar 08 '11 at 04:08
1

Validations would work (Back them up with a unique index!), but there's no way to get a true composite primary key in vanilla Rails. If you want a real composite primary key, you're going to need a gem/plugin - composite_primary_keys is the one I found, but I'm sure there are others.

Hope this helps!

Xavier Holt
  • 14,471
  • 4
  • 43
  • 56
0

Add a UNIQUE constraint to your table across the two columns. Here's a reference for MySQL http://dev.mysql.com/doc/refman/5.0/en/constraint-primary-key.html

SamStephens
  • 5,721
  • 6
  • 36
  • 44