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.
> 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
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').
^^ 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. -- Posted via http://www.ruby-forum.com/.
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.
Frederick Cheung 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)
Jim Ball wrote: > Frederick Cheung 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:-
> 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.
> ^^ > 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.)
> Frederick Cheung 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)?
