# Non integer primary key

**URL:** <https://www.ruby-forum.com/t/non-integer-primary-key/108423>\
**Category:** Rails\
**Created:** [July 3, 2007, 11:07am UTC](https://www.ruby-forum.com/t/non-integer-primary-key/108423 "2007-07-03T11:07:37Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![migo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/f14d63/32.png) [@migo](https://www.ruby-forum.com/u/migo)\
**Post date:** [July 3, 2007, 11:07am UTC](https://www.ruby-forum.com/t/non-integer-primary-key/108423/1 "2007-07-03T11:07:37Z")

</div>

Hallo,

I need to define a non integer primary key in the migrations an I am  
kind of stuck here. All I seem to be able to do is either omit the  
creation of the key by setting :id =\> false, or choose a differnet key  
column by setting :primary\_key =\> ‘xxx’. Then a different primary key  
with an integer datatype is created.

How do I force the schema definition to create a string primary key.  
And before you ask, No I cannot change my data structure, it is  
imported data, that will update itself every once in a while!

The primary key will be referenced via a foreign key from a different  
table. I know I can set the 'set\_primary\_key “xxx” ’ in the model  
class, but mysql complains if there is a table without primary key.

Thanks for your help.  
migo

---

<div class="post-metadata">

**Author:** ![migo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/f14d63/32.png) [@migo](https://www.ruby-forum.com/u/migo)\
**Post date:** [July 3, 2007, 4:42pm UTC](https://www.ruby-forum.com/t/non-integer-primary-key/108423/2 "2007-07-03T16:42:44Z")

</div>

migo wrote:

> imported data, that will update itself every once in a while!
> 
> The primary key will be referenced via a foreign key from a different  
> table. I know I can set the 'set\_primary\_key “xxx” ’ in the model  
> class, but mysql complains if there is a table without primary key.
> 
> Thanks for your help.  
> migo

Rails is very opinionated about certain things, and primary keys on the  
tables is one of them.

In your case, I would consider omitting the primary key, and putting  
these non-integer keys into a field called ‘alphacode’ or something  
descriptive, but not ‘id’ (to prevent confusion with typical Rails id  
fields in the future). Then, whenever you want to lookup one of these  
records, do Thing.find\_by\_alphacode(‘XYZ’) isntead of Thing.find(1).  
You can even consider overriding ‘find’ in the model to make this  
seamless, allowing Thing.find(‘XYZ’).

–  
[http://www.5valleys.com/](http://www.5valleys.com/)

---

<div class="post-metadata">

**Author:** ![migo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/f14d63/32.png) [@migo](https://www.ruby-forum.com/u/migo)\
**Post date:** [June 1, 2008, 6:30pm UTC](https://www.ruby-forum.com/t/non-integer-primary-key/108423/3 "2008-06-01T18:30:42Z")

</div>

^^  
Why are they opinionated on this? You should always be able to set a  
primary key and there has never been a popular db consensus that primary  
keys have to be int, bigint nor autoindex’d. Yes you can disable primary  
keys and create your own unique index. It just seems silly they don’t  
enable you to define your own primary (on the basis of opinion  
allegedly). Just because it is database agnostic doesn’t mean they  
disregard the relational db ways , after all ‘agnostic’ really isn’t an  
accurate word but rather a buzz word. I’m using a plugin foreign key  
relationships. It is good to have that extra layer.

---

<div class="post-metadata">

**Author:** ![migo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/f14d63/32.png) [@migo](https://www.ruby-forum.com/u/migo)\
**Post date:** [June 1, 2008, 7:01pm UTC](https://www.ruby-forum.com/t/non-integer-primary-key/108423/4 "2008-06-01T19:01:24Z")

</div>

Paul Thomas wrote:

> ^^  
> Why are they opinionated on this? You should always be able to set a  
> primary key and there has never been a popular db consensus that primary  
> keys have to be int, bigint nor autoindex’d. Yes you can disable primary  
> keys and create your own unique index. It just seems silly they don’t  
> enable you to define your own primary (on the basis of opinion  
> allegedly). Just because it is database agnostic doesn’t mean they  
> disregard the relational db ways , after all ‘agnostic’ really isn’t an  
> accurate word but rather a buzz word. I’m using a plugin foreign key  
> relationships. It is good to have that extra layer.

It’s difficult to redefine the primary key type.

the sqlite adaptor makes it easier by defining a  
“default\_primary\_key\_type”  
[http://github.com/rails/rails/tree/master/activerecord/lib/active\_record/connection\_adapters/sqlite\_adapter.rb#LC374](http://github.com/rails/rails/tree/master/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb#LC374)  
which you could easily overwrite

but more generally, the mysql connection adaptor doesnt do this.

[http://github.com/rails/rails/tree/master/activerecord/lib/active\_record/connection\_adapters/mysql\_adapter.rb#LC194](http://github.com/rails/rails/tree/master/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb#LC194)

> > ActiveRecord::Base.connection.native\_database\_types[:primary\_key]  
> > =\> “int(11) DEFAULT NULL auto\_increment PRIMARY KEY”

but you could overwrite this in your migration, I guess.

---

<div class="post-metadata">

**Author:** ![migo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/f14d63/32.png) [@migo](https://www.ruby-forum.com/u/migo)\
**Post date:** [June 1, 2008, 7:06pm UTC](https://www.ruby-forum.com/t/non-integer-primary-key/108423/5 "2008-06-01T19:06:43Z")

</div>

You could just write the sql to create the table

Fred

---

<div class="post-metadata">

**Author:** ![migo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/f14d63/32.png) [@migo](https://www.ruby-forum.com/u/migo)\
**Post date:** [June 1, 2008, 7:34pm UTC](https://www.ruby-forum.com/t/non-integer-primary-key/108423/6 "2008-06-01T19:34:23Z")

</div>

Frederick C. wrote:

> You could just write the sql to create the table
> 
> Fred

that’s too easy Froddy.

---

<div class="post-metadata">

**Author:** ![migo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/f14d63/32.png) [@migo](https://www.ruby-forum.com/u/migo)\
**Post date:** [July 12, 2008, 8:23pm UTC](https://www.ruby-forum.com/t/non-integer-primary-key/108423/7 "2008-07-12T20:23:24Z")

</div>

Jim Ball wrote:

> Frederick C. wrote:
> 
> > You could just write the sql to create the table
> > 
> > Fred
> 
> I’ve done something similar for legacy DB that cannot follow the Rails  
> convention explicitly in postgreSQL in the past:-
> 
> So, in the migrations file:-
> 
> def self.up  
> # -----------------------------------------------------  
> # This is example postgreSQL specific DDL, have a think  
> # what is right for you  
> # -----------------------------------------------------  
> execute \<\<-EOF  
> create table public.\<my\_pluralized\_model\_name\> (  
> mykey varchar(255) not null unique,  
> firstvalue varchar(255) not null,  
> secondvalue varchar(2000),  
> primary key (mykey)  
> );  
> EOF  
> end
> 
> So, substitute \<my\_pluralized\_model\_name\> with your model name  
> “pluralized”.  
> You should know the score on this one by now.
> 
> You also seem to need to add a line in the model file itself to allow  
> rails to  
> pick up the new string identifier “mykey” as the ID:-
> 
> So …
> 
> class MyModelName \< ActiveRecord::Base  
> set\_primary\_key “mykey”  
> end
> 
> Then, if you used the standard script/generate scaffold MyModelName,  
> you’ll have  
> a controller. All methods of which will work (for you to test with),  
> apart from the new, which I’m trying to figure at them moment.
> 
> With a non-standard PK defined at the table level, I’m getting
> 
> “WARNING: Can’t mass-assign these protected attributes: mykey”
> 
> I reckon this is because by default rails doesn’t like assignment of  
> PKs, but instead likes to assign PK values itself using aut-incrementing  
> DB specific single instance mechs, which don’t scale.
> 
> If there are any serious Rails dudes out there who know how I can crack  
> that last bit, it would save going back to the customer and persuading  
> them to re-engineer a legacy DB (which they won’t do)
> 
> Anyways, HTH

Right figured it myself.

The default controller you get for CRUD uses mass assignment in its  
create method, we know this fails, so avoid it.

Instead of the default mass assignment in the create method:-

```
@mymodel = MyModel.new(params[:mymodel])

```

Do the “new” first, and then pick out your model attribute values from  
the  
params passed into the create method, and then save:-

```
@mymodel = MyModel.new

got_details = params[:mymodel]
@my_model.mykey = gotdetails["mykey"]
@my_model.firstvalue = gotdetails["firstvalue"]
@my_model.secondvalue = gotdetails["secondvalue"]

@my_model.save

```

Okay, that will do it.

---

<div class="post-metadata">

**Author:** ![migo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/f14d63/32.png) [@migo](https://www.ruby-forum.com/u/migo)\
**Post date:** [July 12, 2008, 7:11pm UTC](https://www.ruby-forum.com/t/non-integer-primary-key/108423/8 "2008-07-12T19:11:02Z")

</div>

Frederick C. wrote:

> You could just write the sql to create the table
> 
> Fred

I’ve done something similar for legacy DB that cannot follow the Rails  
convention explicitly in postgreSQL in the past:-

So, in the migrations file:-

def self.up  
# -----------------------------------------------------  
# This is example postgreSQL specific DDL, have a think  
# what is right for you  
# -----------------------------------------------------  
execute \<\<-EOF  
create table public.\<my\_pluralized\_model\_name\> (  
mykey varchar(255) not null unique,  
firstvalue varchar(255) not null,  
secondvalue varchar(2000),  
primary key (mykey)  
);  
EOF  
end

So, substitute \<my\_pluralized\_model\_name\> with your model name  
“pluralized”.  
You should know the score on this one by now.

You also seem to need to add a line in the model file itself to allow  
rails to  
pick up the new string identifier “mykey” as the ID:-

So …

class MyModelName \< ActiveRecord::Base  
set\_primary\_key “mykey”  
end

Then, if you used the standard script/generate scaffold MyModelName,  
you’ll have  
a controller. All methods of which will work (for you to test with),  
apart from the new, which I’m trying to figure at them moment.

With a non-standard PK defined at the table level, I’m getting

“WARNING: Can’t mass-assign these protected attributes: mykey”

I reckon this is because by default rails doesn’t like assignment of  
PKs, but instead likes to assign PK values itself using aut-incrementing  
DB specific single instance mechs, which don’t scale.

If there are any serious Rails dudes out there who know how I can crack  
that last bit, it would save going back to the customer and persuading  
them to re-engineer a legacy DB (which they won’t do)

Anyways, HTH

---

<div class="post-metadata">

**Author:** ![migo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/f14d63/32.png) [@migo](https://www.ruby-forum.com/u/migo)\
**Post date:** [October 19, 2010, 7:51pm UTC](https://www.ruby-forum.com/t/non-integer-primary-key/108423/9 "2010-10-19T19:51:04Z")

</div>

Frederick C. wrote in post #682577:

> You could just write the sql to create the table
> 
> Fred

True, but be careful if you plan on using schema.rb to deploy your  
application. schema.rb is generated by rails from the database state.  
If you use a non integer primary key in your database, schema.rb will  
still generate an integer column.

---

<div class="post-metadata">

**Author:** ![migo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/f14d63/32.png) [@migo](https://www.ruby-forum.com/u/migo)\
**Post date:** [October 19, 2010, 8:21pm UTC](https://www.ruby-forum.com/t/non-integer-primary-key/108423/10 "2010-10-19T20:21:09Z")

</div>

Paul Thomas wrote in post #682570:

> ^^  
> Why are they opinionated on this? You should always be able to set a  
> primary key and there has never been a popular db consensus that primary  
> keys have to be int, bigint nor autoindex’d.

Actually, there has been. Surrogate primary keys should always be  
automatically generated, and the use of a surrogate primary key (rather  
than a domain key) has the advantage that you know it will never ever  
change over the life of the record.

And the easiest way to autogenerate surrogate keys is to use an  
incrementing integer or GUID.

> Yes you can disable primary  
> keys and create your own unique index. It just seems silly they don’t  
> enable you to define your own primary (on the basis of opinion  
> allegedly).

It’s not silly. You don’t ever need to define your own primary key.

> Just because it is database agnostic doesn’t mean they  
> disregard the relational db ways ,

Right! No relational DB practices are being disregarded here.

> after all ‘agnostic’ really isn’t an  
> accurate word but rather a buzz word.

Wrong.

> I’m using a plugin foreign key  
> relationships. It is good to have that extra layer.

That creates foreign key constraints in the DB, right? If so, then it’s  
completely irrelevant to this discussion.

(For the record, I use Foreigner for the same purpose.)

## Best,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![migo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/f14d63/32.png) [@migo](https://www.ruby-forum.com/u/migo)\
**Post date:** [October 19, 2010, 8:21pm UTC](https://www.ruby-forum.com/t/non-integer-primary-key/108423/11 "2010-10-19T20:21:57Z")

</div>

Noah D. wrote in post #955514:

> Frederick C. wrote in post #682577:
> 
> > You could just write the sql to create the table
> > 
> > Fred
> 
> True, but be careful if you plan on using schema.rb to deploy your  
> application. schema.rb is generated by rails from the database state.  
> If you use a non integer primary key in your database, schema.rb will  
> still generate an integer column.

Interesting. Could you get around this by setting the schema language  
to SQL (not that I advocate that)?

## Best,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid