shared primary key (using the primary key as a foreign key for has_one association)

rubyonrails-talk
1

Hello, can anyone simply tell me if what i want to do is ok to do with Rails, or will there arise some serious obstacle? If it is ok to do, then i will probably figure out a way ofter some trial and error. Any other suggestion will also be appreciated.

I want to share the primary key among several tables, and then to also use it as the foreign key for has_one associations.

I have models "Person", "Instructor", and "Member" (tables "people", "instructors", and "members"). Instructors and members of an association are people, so i plan to store their personal information in "people" table, and to store a foreign key "person_id" in "instructors" and "members" tables. I also plan to have the associations:

class Person < ActiveRecord::Base   has_one :instructor, :dependent => :destroy   has_one :member, :dependent => :destroy end

class Instructor < ActiveRecord::Base   belongs_to :person end

class Member < ActiveRecord::Base   belongs_to :person end

It is possible for a same person to be an instructor and a member in the same time, or neither of two.

Now i do not need "id" in "instructors" and "members" tables, as i can use "person_id" as the primary key. I plan to create the tables with migrations like these:

class CreateInstructors < ActiveRecord::Migration   def self.up     create_table :instructors, :primary_key => "person_id" do |t|       t.text :presentation       t.binary :photo ...     end   end ... end ... class CreateMembers < ActiveRecord::Migration   def self.up     create_table :members, :primary_key => "person_id" do |t|       t.date :member_since ...     end   end ... end

My question is: is this going to work, or will i encounter some unresolvable issues? (For example, when creating a new Instructor, how to specify its primary key "person_id" to be equal to the "id" of an existing person?)

Thanks.

Alexey.

2

Why do you want to use the same primary key value for multiple tables? Unless you have a really good reason for not following the rails conventions then it is best to stick to them. Your life will be much easier. Just set up the appropriate relationships between the models in the normal way.

Colin

3

Colin, probably my life will be much easier, but much less fun. It is just so natural to use the same primary key in this situation.

4

Colin, probably my life will be much easier, but much less fun.

Are you sure? Trying to force rails away from its preferred conventions is rarely fun. Do it the easy way and use the time saved to indulge in something that is really fun :slight_smile:

It is just so natural to use the same primary key in this situation.

Why?

Colin

5

It is certainly “possible” to do what you’re describing, and yes, the same person could be both an instructor and a member at the same time.

However, I’ll second Colin’s suggestion that you just follow the convention to have a separate id primary key column in both the instructors and members tables. The extra bytes-per-record in your database will be worth the lack of hassle, even if you grow to many millions of records.

Now, if you insist on doing it the way you describe, you’ll need to use the #set_primary_key macro method in your models:

class Instructor set_primary_key “person_id” … end

You’ll also need to be sure to explicitly set the primary key value when creating your instructor and member records. If you do this through your has_one relationship (person.create_instructor for example) it may do this for you. I don’t know for sure, never tried this. Test it out and see.

Likewise, I’m guessing that your relationship definitions (since you’re using the “person_id” name) won’t need any tweaking. But again, test and see (to be sure you won’t need to provide :primary_key and/or :foreign_key options to your belongs_to and/or has_one relationship definitions).

6

It is just so natural to use the same primary key in this situation.

Why?

Well, it seems natural to me, and i often trust my senses :).

I have been playing with it in console for a few minutes now and observing some fun and weird behavior:

i = Instructor.create i.id # => 1 i.person_id # => nil Instructor.find(1) # => NoMethodError: undefined method `eq' for nil:NilClass ... i=Instructor.find_by_person_id(1) i.id # => nil i.person_id # => 1

So far it seem to be possible to find ways around to make it work, but i wonder if i am not making a philosophical mistake indeed, and if it will not break in new versions of rails ...

7

It appears from your console session you never added the #set_primary_key macro method call to you Instructor class. I just duplicated your console session as follows in a test project only with the set_primary_key call as follows:

i = Instructor.create i.id # => 1 i.person_id # => 1 Instructor.find(1) # => #<Instructor person_id: 1, created_at: “2011-03-25 17:24:04”, updated_at: “2011-03-25 17:24:04”> i = Instructor.find_by_person_id(1) # => #<Instructor person_id: 1, created_at: “2011-03-25 17:24:04”, updated_at: “2011-03-25 17:24:04”> i.id # => 1 i.person_id # => 1

I just wanted to point this out. I still suggest following rails conventions but rails is flexible (I have to flex it to support and old, pre-existing database that does NOT follow the conventions).

8

This is some strange new use of the word fun, with which I am not familiar.

Colin

9

Kendall, thank's for your suggestion, it really made "id" and "person_id" equivalent for instructors and stopped the described above weird (and fun) behavior.

Also,

p=Person.create m=p.create_member i=p.create_instructor

work as expected now with setting identical values for p.id, m.id, m.person_id, i.id, and i.person_id.

When an instructor with the value of id equal to p.id already exists, i=p.create_instructor triggers ActiveRecord::StatementInvalid: SQLite3::ConstraintException: PRIMARY KEY must be unique: ...

which is a desired behavior.

I will keep testing, and if i do not encounter something extremely weird, i may stick to this...

Thank you.

10

A relevant question that i think is appropriate for this thread: can anybody please explain to me the purpose of primary_key type in context of migrations: t.primary_key :person_id ?

The migration

class CreateInstructors < ActiveRecord::Migration   def self.up     create_table :instructors do |t|       t.primary_key :person_id       t.text :presentation ...

does not work (SQLite3::SQLException: table "instructors" has more than one primary key: ... during migration), but

class CreateInstructors < ActiveRecord::Migration   def self.up     create_table :instructors, :primary_key => "person_id" do |t|       t.primary_key :person_id       t.text :presentation ...

and

class CreateInstructors < ActiveRecord::Migration   def self.up     create_table :instructors, :primary_key => "person_id" do |t|       t.text :presentation ...

seem to have completely identical effect:-/.

11

Just on more related question (if anyone has time to answer):

if i have set_primary_key 'person_id':

class Instructor < ActiveRecord::Base   set_primary_key 'person_id'   ... end

does it mean that writing

class Person < ActiveRecord::Base   has_one :instructor, :primary_key => 'person_id' end

would be redundant? What is the purpose of :primary_key in has_one options, if one uses set_primary_key ?

Thanks.

12

I have deleted one of my questions because i was apparently confused. Anyway, i do not understand the use of :primary_key parameter in has_one option like in

has_one :something, :primary_key => 'someone_id'

What is it for if the primary key has to be set with set_primary_key => 'someone_id' ?

13

For the situation described in this thread where your Person class’s #has_one :instructor refers to another class (and table) that in fact HAS a column named “person_id” then there is no need to provide the :primary_key option, as this is what it will infer by default.

However, in even weirder situations (like mine for instance where I’m connecting ActiveRecord to an old database that doesn’t come close to following the rails conventions), it is useful. Example (similar to what I have to deal with):

table: tblUser primary key: UserID

table: tblComments primary key: ID # note the inconsistencies I have to deal with foreign key (to tblUser record): PosterID

Here, “comments” belong to “users” (a user has_many comments). My models look like this:

class User < ActiveRecord::Base set_table_name “tblUser” set_primary_key “UserID” has_many :comments, :primary_key => “UserID”, :foreign_key => “PosterID” end

class Comment < ActiveRecord::Base set_table_name “tblCommants” set_primary_key “ID” belongs_to :user, :primary_key => “UserID”, :foreign_key => “PosterID” end

This is an example when you need to use the :primary_key options (and :foreign_key as well). This is because there is NO way rails can infer these names otherwise. However, despite my schema “sucking” (in rails terms) I manage to otherwise use ActiveRecord quite nicely.

Now, just because this is possible, doesn’t mean I’d ever choose to go against conventions. It is beautiful when you can just rely on the defaults and let the rails magic fly!

14

To further clarify (and answer one of the questions above): You must specify the :primary_key option to #has_one (and #has_many) in your model definition if, and only if, the table of the resource that “owns” the other record (i.e., the table associated w/the model that contains the #has_one method call) doesn’t use “id” for its own primary key. The same applies to #belongs_to, :primary_key is only needed when the “owning” resource’s table doesn’t use “id”. So, if a post has many comments and you’re hooking the relationships up, as long as the posts table uses “id” for its primary key, then neither the #has_many inside the Post class or the #belongs_to inside the Comment class will need the :primary_key option. Similarly, you must specify the :foreign_key options to #belongs_to, #has_many and #has_one if and only if the “owned” resource (the one that actually has the foreign key column) doesn’t follow the rails convention for its foreign key column name (lower-case and underscored version of the resource’s class name, which is ideally the same as the singular version of the table name, all with the “_id” suffix). So, as long as the table backing the Comment class (even if the table has a wonky name and #set_table_name is used) has a “post_id” column then :foreign_key options won’t be needed on any of the relationships (on the owner or ownee). In the OP’s situation, the owning table used “id” for its primary key (people table for the Person class). Thus no :primary_key options were needed anywhere. Likewise, the owned tables (instructors for the Instructor class (and the other one…)) used the rails convention for the foreign key names, so :foreign_key options were unnecessary. All this regardless of the fact that the OP happened to be re-using the primary key for the foreign key in the owned resource tables. Now, if one of the owned resources later needed to “own” something themselves (like an instructor having many “students” for example) then those relationships would have required :primary_key options since “id” wasn’t the primary key name.

Now, to answer one of the questions posed further above…

The :primary_key option in migrations vs. the #primary_key method available on the table definition object inside your #create_table call are, for most part, redundant. Also, when it comes to migrations that create new tables, I personally see no need for both and would just always use the :primary_key option to #create_table.

However, if you created a table long ago and for some reason it had no primary key (imagine an old database or a table that used to just be a join table). You might want to “add” a primary key in a new migration. In this case, you definitely won’t be using the :primary_key option to #create_table in your migration. You’ll be modifying the table to add a new primary key column.

Now, the presence of the #primary_key method on a table definition would make sense if this worked:

class AddIdToOldItems < ActiveRecord::Migration def self.up change_table :old_items do |t| t.primary_key :id # other changes … end end def self.down change_table :old_items do |t| # other changes … t.remove :id end end end

However, on a quick test, #primary_key isn’t defined in this context. Instead, you have to use one of: change_table :old_items do |t| t.column :id, :primary_key end Or just add_column :students, :id, :primary_key

So, I guess having #primary_key available in the context in creating a new table is just for extra options? Anyone else care to comment on this?

15

Kendall, thank you for your explanations, especially about migrations.

I removed my other post because i realized that in my situation the primary key used for the association was in fact "id", so it was a bad example.

Still, in your example, i do not understand the need for ":primary_key =>":

Kendall Gifford wrote in post #989309:

class User < ActiveRecord::Base   set_table_name "tblUser"   set_primary_key "UserID"   has_many :comments, :primary_key => "UserID",     :foreign_key => "PosterID" end

was it not working without it?

It seems redundant to write:

class Instructor < ActiveRecord::Base   set_primary_key 'person_id'   has_many :lessons, :primary_key => 'person_id'   ... end

It also does not seem to me to be strictly necessary to add to the above:

class Lesson < ActiveRecord::Base   belongs_to :instructor, :primary_key => 'person_id'   ... end

However, i can philosophically understand the need for ":primary_key =>" on the "belongs_to" side of association, it could serve to make this side more independent from the "has_many" side, and avoid unexpected behavior of Lesson if Instructor is broken ("set_primary_key 'person_id'" deleted). It seems to me that Rails makes it possible to define and use only one side of an association, without defining the other (otherwise the ":inverse_of =>" option probably wouldn't be useful).

I have not properly tested this situation with and without ":primary_key =>", but so far it seems to work without, "set_primary_key 'person_id'" seems to suffice for both sides. So i will try to leave it like this...

P.S. the reason i am torturing Rails like that is that it also has a convention that it should obey the human. Seriously, i just want a nice and clear structure of my database, which would be editable by hand (it is not going to be big), and to use Rails just for a simple interface.

16

It is indeed redundant to have both #set_primary_key and the :primary_key option w/in your #has_many (or #has_one) calls.

You can safely ignore that aspect of my prior posts :). The real code I yanked my examples from actually has a table with a primary key named “ID” (so set_primary_key “ID”) but has another column “UserID” (that is also unique and could have been the primary key instead; crazy old db) that for all relationships is treated as if it were the primary key.

So, in reality my model is more like this:

class User < ActiveRecord::Base set_table_name “tblUser” set_primary_key “ID” has_many :comments, :primary_key => “UserID”, :foreign_key => “PosterID” end

That’s what I get for not simply copy/pasting (then nuking irrelevant details) and hand-coding my example from memory.

Anyhow, your other observation is also dead on. Creating an association on a model doesn’t require you to create the “inverse” association on the other model.

P.S. the reason i am torturing Rails like that is that it also has a convention that it should obey the human. Seriously, i just want a nice and clear structure of my database, which would be editable by hand (it is not going to be big), and to use Rails just for a simple interface.

While I always follow rails conventions when possible (and recommend others do too), I also like to “torture” frameworks to see how flexible they are. After all, there are always occasions where it becomes necessary, such as in my instance where I have to work off of a legacy database that is still concurrently being accessed by legacy software. On the side, however, I’ve instantiated a dozen smaller “toy” or “utility” rails apps where I’ve followed the conventions and idioms religiously to great effect. Love 'em.

17

Thanks for the explanation, it is interesting that the ":primary_key =>" option does not have to point to the primary key!

18

I know this is an old thread, but as it seems to have the best advice I've seen on this subject, I'm going to throw my few cents in here.

I'm strongly leaning in the direction of a shared primary/foreign key for situations where the parent record is a prerequisite for the child record. Effectively, the child record is an aspect of the parent record. It could be modeled using a single parent table that is really wide (i.e. that contains columns for all of the child records), but there may be good reasons not to do that.

The approach I am taking is as follows: Use the normal Rails :id column for both tables.

It appears in testing against Postgresql that one can have an auto-increment primary key and still specify the primary key value when creating a new record. In order to enforce this, I specify `validates :id, presence: true, uniqueness: true` in the model for the child table. If I run into a database that won't let me specify the value for an auto-increment field when creating a new record I'll switch to using `id: false` in the migration and manually create the `id` field as an integer and then specify a unique index on it.

I specify belongs_to on the child record and pass `foreign_key: :id`. I do the same for specifying has_one on the parent record.

There is no need to specify self.primary_key (the replacement for set_primary_key) on the child model since the primary key is still :id.

I'm using a nested singular resource for the child record when routing like so:

  resources :user do     resource :instructor   end

That sets up routes like so:   /users/:id   /users/:user_id/instructor

Because I used :id throughout, when using the route helpers like user_instructor_path, I can pass either a User or an Instructor object and I will still get the correct route.

I'm still experimenting with this approach, but so far I'm reasonably happy with it.

19

My experimental application where i use shared primary/foreign key is still in experimental phase. It mostly works, but i had to use workarounds for saving/updating objects associated through shared primary/foreign key (i do not want to look trough the details now). I still think that a natural database structure (the one that looks natural to me) is more important that Rails conventions.

20

Sure, because you TOTALLY know more about database best practices than everybody else…

–Matt Jones