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

Rails
1

Hello, can anyone please 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 after 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 the 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

On 25 March 2011 16:01, Alexey M. [email protected] wrote:

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.

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

On 25 March 2011 16:47, Alexey M. [email protected] wrote:

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 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).

7

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 …

8

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.

9

On 25 March 2011 17:15, Alexey M. [email protected] wrote:

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

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

Colin

10

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’ ?

11

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:-/.

12

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!

13

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
Instructorclass (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_keyoption 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_tablein 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?

14

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 G. 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.

15

It is indeed redundant to have both #set_primary_key and the
:primary_keyoption 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.

16

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.

17

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

18

On Thursday, 16 May 2013 16:44:20 UTC-4, Ruby-Forum.com User wrote:

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.

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

–Matt J.

19

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