# Foregin Key PostgreSQL, indexing?

**URL:** <https://www.ruby-forum.com/t/foregin-key-postgresql-indexing/133634>\
**Category:** Rails\
**Created:** [April 2, 2008, 5:52am UTC](https://www.ruby-forum.com/t/foregin-key-postgresql-indexing/133634 "2008-04-02T05:52:05Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![rashantha](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/b9bd4f/32.png) [@rashantha](https://www.ruby-forum.com/u/rashantha)\
**Post date:** [April 2, 2008, 5:52am UTC](https://www.ruby-forum.com/t/foregin-key-postgresql-indexing/133634/1 "2008-04-02T05:52:05Z")

</div>

i posted this but did not get a response.

i would like to add that i am using postgresql. if i use the primary  
key i can do look as below but can not do with another type of key.  
do i need to index these?

i also read this but it didn’t lead to the solution.  
[http://groups.google.com/group/rubyonrails-talk/browse\_thread/thread/eb70d8c45a6a3837/864be680bb9977e6?lnk](http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/eb70d8c45a6a3837/864be680bb9977e6?lnk)

model user  
id (automatic primary)  
fname  
lname  
user\_rrid (foreign key) - string “123456”  
model email  
id (automatic primary)  
e\_addresss  
user\_rrid (foreign key) - string “123456”  
e\_type  
i would like to find a user by fname  
@user = User.find\_by\_fname(“John”)  
then use…  
@user.email.e\_address  
@user.email.e\_type  
i would also like to go the other way. search email based on  
e\_address  
and find the user.  
can you please show how the user.rb and email.rb files should be set  
up. i have looked thru the docs and googled and everything i try does  
not work.  
i have tried using foreign\_key but it does not work.  
thanks.

[http://groups.google.com/group/rubyonrails-talk/browse\_thread/thread/eb70d8c45a6a3837/864be680bb9977e6?lnk=gst&q=foreign+key#](http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/eb70d8c45a6a3837/864be680bb9977e6?lnk=gst&q=foreign+key#)

---

<div class="post-metadata">

**Author:** ![rashantha](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/b9bd4f/32.png) [@rashantha](https://www.ruby-forum.com/u/rashantha)\
**Post date:** [April 2, 2008, 6:03am UTC](https://www.ruby-forum.com/t/foregin-key-postgresql-indexing/133634/2 "2008-04-02T06:03:48Z")

</div>

On Tue, 2008-04-01 at 20:51 -0700, rashantha wrote:

> model user  
> @user = User.find\_by\_fname(“John”)  
> thanks.

* * *

doesn’t sound like a foreign key or index issue at all.

model user  
has\_one :email

create\_table “users”, :force =\> true do |t|  
t.column “fname”, :string, :limit =\> 25  
t.column “email\_id”, :string, :limit =\> 25, :null =\> false  
end

model email  
belongs\_to :user

create\_table “emails”, :force =\> true do |t|  
t.column “e\_address”, :string, :limit =\> 32  
t.column “e\_type”, :string, :limit =\> 25  
t.column “user\_id”, :string, :limit =\> 25, :null =\> false  
end

Craig

---

<div class="post-metadata">

**Author:** ![rashantha](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/b9bd4f/32.png) [@rashantha](https://www.ruby-forum.com/u/rashantha)\
**Post date:** [April 2, 2008, 4:21pm UTC](https://www.ruby-forum.com/t/foregin-key-postgresql-indexing/133634/3 "2008-04-02T16:21:58Z")

</div>

On Apr 1, 9:03 pm, Craig W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> * * *
> 
> doesn’t sound like a foreign key or index issue at all.
> 
> model user  
> has\_one :email
> 
> create\_table “users”, :force =\> true do |t|  
> t.column “fname”, :string, :limit =\> 25  
> t.column “email\_id”, :string, :limit =\> 25, :null =\> false  
> end

here is the problem i don’t want to use the automatic primary key  
(email\_id) or (user\_id)  
i have created user\_rrid and email\_rrid to have a value of string

does this have to be changed to an integer?

can’t you use this as a foreign key?

---

<div class="post-metadata">

**Author:** ![rashantha](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/b9bd4f/32.png) [@rashantha](https://www.ruby-forum.com/u/rashantha)\
**Post date:** [April 2, 2008, 4:59pm UTC](https://www.ruby-forum.com/t/foregin-key-postgresql-indexing/133634/4 "2008-04-02T16:59:30Z")

</div>

On Wed, 2008-04-02 at 07:10 -0700, rashantha wrote:

> > create\_table “users”, :force =\> true do |t|  
> > t.column “fname”, :string, :limit =\> 25  
> > t.column “email\_id”, :string, :limit =\> 25, :null =\> false  
> > end
> 
> here is the problem i don’t want to use the automatic primary key  
> (email\_id) or (user\_id)  
> i have created user\_rrid and email\_rrid to have a value of string
> 
> does this have to be changed to an integer?

* * *

## no - a key can be anything unique

> can’t you use this as a foreign key?

* * *

I would imagine that you can. I’ll let someone else who’s used foreign  
key’s answer from here on out since I have never needed to use them.

Craig

---

<div class="post-metadata">

**Author:** ![rashantha](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/b9bd4f/32.png) [@rashantha](https://www.ruby-forum.com/u/rashantha)\
**Post date:** [April 2, 2008, 6:07am UTC](https://www.ruby-forum.com/t/foregin-key-postgresql-indexing/133634/5 "2008-04-02T06:07:20Z")

</div>

On Tue, 2008-04-01 at 21:03 -0700, Craig W. wrote:

> > i would like to find a user by fname  
> > i have tried using foreign\_key but it does not work.  
> > end
> 
> model email  
> belongs\_to :user
> 
> create\_table “emails”, :force =\> true do |t|  
> t.column “e\_address”, :string, :limit =\> 32  
> t.column “e\_type”, :string, :limit =\> 25  
> t.column “user\_id”, :string, :limit =\> 25, :null =\> false  
> end

* * *

sorry…correction

both email\_id and user\_id fields should be :integer, not :string

Craig

---

<div class="post-metadata">

**Author:** ![rashantha](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/b9bd4f/32.png) [@rashantha](https://www.ruby-forum.com/u/rashantha)\
**Post date:** [April 3, 2008, 12:31am UTC](https://www.ruby-forum.com/t/foregin-key-postgresql-indexing/133634/6 "2008-04-03T00:31:12Z")

</div>

Woops, rails does handle it correctly. The problem was you have to  
specify foreign key names in lower case or all things turn to custard.

Mark

On Apr 3, 438 am, James B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![rashantha](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/b9bd4f/32.png) [@rashantha](https://www.ruby-forum.com/u/rashantha)\
**Post date:** [April 2, 2008, 5:38pm UTC](https://www.ruby-forum.com/t/foregin-key-postgresql-indexing/133634/7 "2008-04-02T17:38:42Z")

</div>

It seems to me that you are confusing several distinct issues.

1. Primary Keys. As distributed, Rails only really works with surrogate  
integer keys, however there are plugins that accommodate so-called  
‘natural’ primary keys. Surrogate key values are assigned by the DBMS  
via a sequencer. Primary keys are automatically given the additional  
attribute of an INDEX UNIQUE. By default, Rails defines primary keys to  
have the attribute name ‘id’ but you can override this in environment.rb  
or in the migration

2. Indexes are DBMS specific processes to speedily retrieve subsets of  
data. These can be defined in Rails migrations and are independent of  
keys. They can be defined as UNIQUE or not and a single index may span  
multiple columns. Indexes are not directly accessible via FIND, the  
DBMS planner decides whether or not a search by index makes sense.

3. Foreign Key lookups are constrained to the primary key of the  
reference table. Thus they must have (or be cast to) the same data type  
as the reference key. Rails does not employ DBMS foreign key  
constraints, rather ActiveRecord uses a FIND followed by an INSERT to  
enforce this requirement, a practice which can fail under high loads. By  
default, Rails considers a foreign key field to possess the pattern  
\_id but this can be modified in environment.rb or overridden  
in the model itself.

4. SQL FINDs require neither a key nor an index, only an attribute and a  
value. The DBMS planner decides on the basis of the schema and the  
statistical data it possesses how best to conduct the search and return  
the tuples. It is often the case that with small tables a planner will  
simply do a serial search of the entire table in memory rather than  
bother with the indices at all.

What I believe that you should do in this case is ignore INDEXES  
altogether until you have your design finalized and performance  
experience indicates that an INDEX on one of your FIND attributes might  
improve response time. The FIND will work with or without an index.  
However, if the issue is one of constraining the use of a particular  
fname to just one instance then an INDEX UNIQUE is really the only way  
to go. (If UNIQUEness is the goal then I have no idea why you would  
place such a restriction of fname alone, it seems to me that a composite  
key on lname+fname is a far better, albeit not very good, approach).

You should also just stick with the Rails defaults of integer valued  
surrogate keys. They work just fine in almost every case even if the  
concept may disturb some.

Your migrations should look something like this:

class CreateUsers \< ActiveRecord::Migration  
def self.up  
create\_table :users do |t|  
t.string :fname, :null =\> false,  
:limit =\> 40  
t.string :lname, :null =\> false,  
:limit =\> 40  
end

```
# Constrain fname in DBMS
add_index :users, :fname,
          :name => :idxU_users_fname,
          :unique => true

```

end

def self.down  
remove\_index :userss, :name =\> :idxU\_users\_fname  
drop\_table :users  
end  
end

class CreateEmail \< ActiveRecord::Migration  
def self.up  
create\_table :emails do |t|  
t.string :e\_address, :null =\> false  
t.string :e\_type, :null =\> false  
t.integer :user\_id, :null =\> false  
end

end

def self.down  
drop\_table :emails  
end  
end

The your models look something like this:

class User \< ActiveRecord::Base

has\_many :emails, :dependent =\> :destroy

validates\_presence\_of :fname  
validates\_presence\_of :lname

# note that validates\_uniqueness\_of does not enforce

# but only checks uniqueness. It is possible to encounter

# a race condition where the entity name is taken between

# the check and the insert.

validates\_uniqueness\_of :fname

end

class Email \< ActiveRecord::Base

belongs\_to :user

validates\_presence\_of :e\_address  
validates\_presence\_of :e\_type

end

Your FINDs belong in the appropriate controllers, either  
users\_controller.rb or emails\_controller.rb. There are also routes to  
consider as email is clearly a nested resource of users. What you end  
up with in a controller would look something like this:

…  
@emails = @emails.find(:all, :conditions =\> [ :fname =?, @user.fname  
])  
…

There are a number of Rails tutorials and screencasts on this. Take a  
look at [railscasts.com](http://railscasts.com) and [peepcode.com](http://peepcode.com). Just bear in mind that with  
SQL, and therefore in Rails, you can always FIND on any attribute  
defined in a table not just those that are designated as being keys or  
indices.

HTH

---

<div class="post-metadata">

**Author:** ![rashantha](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/b9bd4f/32.png) [@rashantha](https://www.ruby-forum.com/u/rashantha)\
**Post date:** [April 3, 2008, 4:44am UTC](https://www.ruby-forum.com/t/foregin-key-postgresql-indexing/133634/8 "2008-04-03T04:44:08Z")

</div>

In Rails (ActiveRecord), it’s better to use integers as primary and  
foreign keys. Rails doesn’t seem to like things much if this isn’t the  
case.

Julian.

Learn Ruby on Rails! CHECK OUT THE FREE VIDS (LIMITED TIME) NEW VIDEO  
(#2) OUT NOW!  
[http://sensei.zenunit.com/](http://sensei.zenunit.com/)