# Concatenated key

**URL:** <https://www.ruby-forum.com/t/concatenated-key/60950>\
**Category:** Rails\
**Created:** [May 18, 2006, 8:36pm UTC](https://www.ruby-forum.com/t/concatenated-key/60950 "2006-05-18T20:36:17Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Guillaume\_F](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/5fc32e/32.png) [@Guillaume\_F](https://www.ruby-forum.com/u/Guillaume_F)\
**Post date:** [May 18, 2006, 8:36pm UTC](https://www.ruby-forum.com/t/concatenated-key/60950/1 "2006-05-18T20:36:17Z")

</div>

Hi all,

I started playing a couple days ago with Rails and it find it very  
interesting.  
But I’m a bit stuck with making it work with a concatenated primary key.

I want to use my own names for the DB fields and I’m using SQL Server  
2005.

My tables look roughly like this:

CREATE TABLE USERS (  
USR\_ID VARCHAR(25) NOT NULL PRIMARY KEY,  
USR\_PASS VARCHAR(25)  
)

CREATE TABLE SECTOR (  
SECT\_ID TINYINT NOT NULL PRIMARY KEY,  
SECT\_NAME VARCHAR(25)  
)

CREATE TABLE PERMISSION (  
FK\_USR\_ID VARCHAR(25) NOT NULL,  
FK\_SECT\_ID TINYINT NOT NULL  
)

ALTER TABLE PERMISSION  
ADD CONSTRAINT PK\_PERMISSION  
PRIMARY KEY (FK\_USR\_ID,FK\_SECT\_ID)

And my models look like this:  
class User \< ActiveRecord::Base  
set\_primary\_key “USR\_ID”

```
def self.table_name() "USERS" end
has_many :permission

```

end

class Sector \< ActiveRecord::Base  
set\_primary\_key “SECT\_ID”

```
def self.table_name() "SECTOR" end
has_many :permission

```

end

class Permission \< ActiveRecord::Base  
set\_primary\_key “FK\_USR\_ID,FK\_SECT\_ID”

```
def self.table_name() "PERMISSION" end
belongs_to :User
belongs_to :Sector

```

end

INSERTing permission data is pretty easy:  
@perm = Permission.new(“FK\_USR\_ID” =\> @user.id, “FK\_SECT\_ID” =\>  
@sector.id)  
@perm.save

## However, SELECTing a permission doesn’t work:

## @perm = Permission.find(“FK\_USR\_ID” =\> @user.id, “FK\_SECT\_ID” =\> @sector.id) gives: ArgumentError (Unknown key(s): FK\_USR\_ID, FK\_SECT\_ID): c:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.3.1/lib/active\_support/core\_ext/hash/keys.rb:48:in `assert_valid_keys' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/base.rb:1323:in`validate\_find\_options’ c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active\_record/base.rb:376:in `find’

## @perm = Permission.find(@user.id,@sector.id) gives: DBI::DatabaseError: Execute OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server Incorrect syntax at ‘,’. HRESULT error code:0x80020009 An exception occured.: SELECT \* FROM PERMISSION WHERE (PERMISSION.FK\_USR\_ID,FK\_SECT\_ID IN (‘gfk’,3)) e[0m

Any idea on how to do it?

Thanks a lot,  
GFK’s

---

<div class="post-metadata">

**Author:** ![Guillaume\_F](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/5fc32e/32.png) [@Guillaume\_F](https://www.ruby-forum.com/u/Guillaume_F)\
**Post date:** [May 18, 2006, 9:09pm UTC](https://www.ruby-forum.com/t/concatenated-key/60950/2 "2006-05-18T21:09:02Z")

</div>

On May 18, 2006, at 11:36 AM, Guillaume F. wrote:

> I started playing a couple days ago with Rails and it find it very  
> interesting.  
> But I’m a bit stuck with making it work with a concatenated primary  
> key.

Hello! and welcome to Rails.

> CREATE TABLE SECTOR (  
> ADD CONSTRAINT PK\_PERMISSION  
> PRIMARY KEY (FK\_USR\_ID,FK\_SECT\_ID)
> 
> And my models look like this:  
> class User \< ActiveRecord::Base  
> set\_primary\_key “USR\_ID”
> 
> def self.table\_name() “USERS” end  
> has\_many :permission  
> end

class User \< ActiveRecord::Base  
self.table\_name = ‘USERS’  
self.primary\_key = ‘USR\_ID’

has\_and\_belongs\_to\_many :sectors, :join\_table =\>  
‘permission’, :foreign\_key =\> ‘FK\_USR\_ID’, :association\_foreign\_key  
=\> ‘FK\_SECT\_ID’  
end

> class Sector \< ActiveRecord::Base  
> set\_primary\_key “SECT\_ID”
> 
> def self.table\_name() “SECTOR” end  
> has\_many :permission  
> end

class Sector \< ActiveRecord::Base  
self.table\_name = ‘SECTOR’  
self.primary\_key = ‘SECT\_ID’

has\_and\_belongs\_to\_many :users, :join\_table =\>  
‘permission’, :foreign\_key =\> ‘FK\_SECT\_ID’, :association\_foreign\_key  
=\> ‘FK\_USR\_ID’  
end

> class Permission \< ActiveRecord::Base  
> set\_primary\_key “FK\_USR\_ID,FK\_SECT\_ID”
> 
> def self.table\_name() “PERMISSION” end  
> belongs\_to :User  
> belongs\_to :Sector  
> end

Compound primary keys are not supported. Add a surrogate key to the  
permission table if you want to represent it with an Active Record  
class. It will also allow you to use has\_many :through, which is more  
flexible & expressive than has\_and\_belongs\_to\_many.

Best,  
jeremy

---

<div class="post-metadata">

**Author:** ![Guillaume\_F](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/5fc32e/32.png) [@Guillaume\_F](https://www.ruby-forum.com/u/Guillaume_F)\
**Post date:** [May 19, 2006, 12:45am UTC](https://www.ruby-forum.com/t/concatenated-key/60950/3 "2006-05-19T00:45:27Z")

</div>

Jeremy K. wrote:

> Compound primary keys are not supported. Add a surrogate key to the  
> permission table if you want to represent it with an Active Record  
> class. It will also allow you to use has\_many :through, which is more  
> flexible & expressive than has\_and\_belongs\_to\_many.

Thanks a lot Jeremy, it works now with a surrogate key.

It’s not as elegant as I would have hoped (I have to use find\_by\_sql  
instead of find) but it’s working well.

Thanks again,  
GFK’s

---

<div class="post-metadata">

**Author:** ![Guillaume\_F](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/5fc32e/32.png) [@Guillaume\_F](https://www.ruby-forum.com/u/Guillaume_F)\
**Post date:** [May 19, 2006, 1:11am UTC](https://www.ruby-forum.com/t/concatenated-key/60950/4 "2006-05-19T01:11:21Z")

</div>

On May 18, 2006, at 3:45 PM, Guilllaume Filion wrote:

> Jeremy K. wrote:
> 
> > Compound primary keys are not supported. Add a surrogate key to the  
> > permission table if you want to represent it with an Active Record  
> > class. It will also allow you to use has\_many :through, which is more  
> > flexible & expressive than has\_and\_belongs\_to\_many.
> 
> Thanks a lot Jeremy, it works now with a surrogate key.
> 
> It’s not as elegant as I would have hoped (I have to use find\_by\_sql  
> instead of find) but it’s working well.

Ah, too bad - tou can almost always use find. Reply with the updated  
code and perhaps we can elegant it up 🙂

jeremy

---

<div class="post-metadata">

**Author:** ![Guillaume\_F](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/5fc32e/32.png) [@Guillaume\_F](https://www.ruby-forum.com/u/Guillaume_F)\
**Post date:** [May 19, 2006, 3:20am UTC](https://www.ruby-forum.com/t/concatenated-key/60950/5 "2006-05-19T03:20:48Z")

</div>

I’m aware this isn’t always possible, but if you can change your table  
names and stuff, rails will be able to work things out for you. Your  
USERS table should be all lower case (I’m not sure if that’s  
required… anyone know for definite? but it is convention), and it’s  
primary key should be ‘id’. Same with your SECTORS table. If you don’t  
want to specify the table name for the join table, it should be called  
sectors\_users, but you’ll probably want to leave that as permission,  
because it makes more sense. The foreign keys should then be ‘user\_id’  
and ‘sector\_id’. I’ve been using the word should, but of course, you  
can do it how you want… it’s just that doing it this way requires  
less code.

If you follow these conventions, your model can be as simple as:

class User \< ActiveRecord::Base  
has\_and\_belongs\_to\_many :sectors, :join\_table =\> ‘permission’  
end

class Sector \< ActiveRecord::Base  
has\_and\_belongs\_to\_many :users, join\_table =\> ‘permission’  
end

-Nathan

---

<div class="post-metadata">

**Author:** ![Guillaume\_F](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/5fc32e/32.png) [@Guillaume\_F](https://www.ruby-forum.com/u/Guillaume_F)\
**Post date:** [May 19, 2006, 5:24pm UTC](https://www.ruby-forum.com/t/concatenated-key/60950/6 "2006-05-19T17:24:12Z")

</div>

unknown wrote:

> I’m aware this isn’t always possible, but if you can change your table  
> names and stuff, rails will be able to work things out for you. […]  
> I’ve been using the word should, but of course, you  
> can do it how you want… it’s just that doing it this way requires  
> less code.
> 
> If you follow these conventions, your model can be as simple as:
> 
> class User \< ActiveRecord::Base  
> has\_and\_belongs\_to\_many :sectors, :join\_table =\> ‘permission’  
> end
> 
> class Sector \< ActiveRecord::Base  
> has\_and\_belongs\_to\_many :users, join\_table =\> ‘permission’  
> end

I don’t know. I’m usually quite pedantic with my database design and I  
must admit that I don’t find the DB design proposed by rails especially  
interesting. But you make a very good point by showing how simple the  
model would be. And since I’m learning rails I should be more open  
minded about its way of doing things.

Before I change my whole DB design, could you provide me with an example  
on how to create and find a permission using this new model?

Thanks a lot,  
GFK’s

---

<div class="post-metadata">

**Author:** ![Guillaume\_F](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/5fc32e/32.png) [@Guillaume\_F](https://www.ruby-forum.com/u/Guillaume_F)\
**Post date:** [May 19, 2006, 5:13pm UTC](https://www.ruby-forum.com/t/concatenated-key/60950/7 "2006-05-19T17:13:23Z")

</div>

Jeremy K. wrote:

> On May 18, 2006, at 3:45 PM, Guilllaume Filion wrote:
> 
> > It’s not as elegant as I would have hoped (I have to use find\_by\_sql  
> > instead of find) but it’s working well.
> 
> Ah, too bad - tou can almost always use find. Reply with the updated  
> code and perhaps we can elegant it up 🙂

Thanks for the offer. Let’s give it a try.

I’ve been playing in ruby/rails for 5 days, so I’m pretty sure that you  
can make it better… 🙂

Let me explain quickly how it works:  
list show a list of users (duh), clicking on a user gives the details  
page.  
The details pages has a checkbox for each sector, checked if the user  
has access to that sector. To do this I’m using the following piece of  
(ugly) code:

\<%= start\_form\_tag :action =\> ‘modify’, :id =\> @User.id %\>

\<% @Sectors.each do |Sector| %\> \>\<%=Sector.SECT\_NAME%\> \<% end %\>

\<%= submit\_tag "Modify" %\>

\<%= end\_form\_tag %\>

The modify code is in the controller (and quite ugly too):

class UserController \< ApplicationController  
after\_filter :set\_charset  
layout “standard-layout”

def set\_charset  
@headers[“Content-Type”] ||= “text/html; charset=iso-8859-1”  
end

def list  
@Users = User.find\_all  
end

def details  
@User = User.find(@params[“id”])  
@Sectors = Sector.find\_all  
end

def modify  
@User = User.find(@params[“id”])  
@Sectors = Sector.find\_all  
@Sectors.each do |Sector|  
if (@params[Sector.SECT\_NAME]) then  
@nb = Permission.count\_by\_sql(“SELECT COUNT(_) FROM PERMISSION  
WHERE FK\_USR\_ID=’" + @User.id.to\_s + “’ AND FK\_SECT\_ID=” +  
Sector.id.to\_s)  
if (@nb == 0) then  
 @perm = Permission.new(“FK\_USR\_ID” =\> @User.id, “FK\_SECT\_ID”  
=\> Sector.id)  
 @perm.save  
end  
else  
 @nb = Permission.count\_by\_sql("SELECT COUNT(_) FROM PERMISSION  
WHERE FK\_USR\_ID=’” + @User.id.to\_s + “’ AND FK\_SECT\_ID=” +  
Sector.id.to\_s)  
if (@nb == 1) then  
Permission.find\_by\_sql(“SELECT \* FROM PERMISSION WHERE  
FK\_USR\_ID=’” + @User.id.to\_s + “’ AND FK\_SECT\_ID=” +  
Sector.id.to\_s).first.destroy  
end  
end  
end  
redirect\_to :action =\> ‘details’, :id =\> @User  
end  
end

Thanks a lot!  
GFK’s

---

<div class="post-metadata">

**Author:** ![Guillaume\_F](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/5fc32e/32.png) [@Guillaume\_F](https://www.ruby-forum.com/u/Guillaume_F)\
**Post date:** [May 19, 2006, 8:18pm UTC](https://www.ruby-forum.com/t/concatenated-key/60950/8 "2006-05-19T20:18:35Z")

</div>

On May 19, 2006, at 8:24 AM, Guillaume F. wrote:

> > class User \< ActiveRecord::Base  
> > interesting. But you make a very good point by showing how simple the  
> > model would be. And since I’m learning rails I should be more open  
> > minded about its way of doing things.
> 
> Before I change my whole DB design, could you provide me with an  
> example  
> on how to create and find a permission using this new model?

user = User.find(:first)  
sector = Sector.find(:first)

# Permit

user.sectors \<\< sector

# Deny

user.sectors.delete(sector)

# Permitted?

user.sectors.include?(sector)

I understand how the database conventions can feel abrasive, but the  
benefits are delightful and manyfold.

jeremy

---

<div class="post-metadata">

**Author:** ![Guillaume\_F](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/5fc32e/32.png) [@Guillaume\_F](https://www.ruby-forum.com/u/Guillaume_F)\
**Post date:** [May 19, 2006, 10:23pm UTC](https://www.ruby-forum.com/t/concatenated-key/60950/9 "2006-05-19T22:23:19Z")

</div>

Jeremy K. wrote:

> # Permit
> 
> user.sectors \<\< sector
> 
> # Deny
> 
> user.sectors.delete(sector)
> 
> # Permitted?
> 
> user.sectors.include?(sector)

Wow! Impressive. It’s certainly worth changing the field and table  
names.

The code is much better looking now:  
def modify  
@user = User.find(@params[“id”])  
Sector.find\_all.each do |sector|  
if (@params[sector.SECT\_NAME]) then  
@user.sectors \<\< sector if (not @user.sectors.include?(sector))  
else  
@user.sectors.delete(sector) if (@user.sectors.include?(sector))  
end  
end  
redirect\_to :action =\> ‘details’, :id =\> @user  
end

Many thanks for your help and have a good week-end,  
GFK’s