For the create_table though, I'm not sure there is a way to specify options
that go inside the parenthesis.
For example this fails because it puts deferrable on the outside of the
parenths:
create_table(:test3, :options => 'deferrable') do |t|
t.string :some_id
end
$ rake db:migrate
== TestMigration: migrating
==================================================
-- create_table(:test3, {:options=>"deferrable"})
rake aborted!
An error has occurred, this and all later migrations canceled:
PG::Error: ERROR: syntax error at or near "deferrable"
LINE 1: ...al primary key, "some_id" character varying(255)) deferrable
Maybe there should be a concept like column_options: 'deferrable'
create_table(:test3, :column_options => 'deferrable') do |t|
t.string :some_id
end
?
On Wednesday, November 28, 2012 11:55:57 AM UTC-5, Gary Weaver wrote:
>
> afaik using foreigner is still the recommended way to handle fkeys in
> Rails. Here is the pull request in foreigner where they discuss deferrable
> a few years ago: https://github.com/matthuhiggins/foreigner/pull/32
>
> Using Foreigner v0.9.1 or later and using structure.sql vs. schema.rb then
> can specify :options in foreigner's add_foreign_key, e.g.
>
> add_foreign_key(:employees, :companies, :options => 'deferrable')
>
> Related rails ticket which had no follow through, mostly related to
> deferred use in fixtures: https://github.com/rails/rails/issues/5523
>
> Unrelated, but for those interested, sequel just added support for
> deferrable constraints in pg:
> https://github.com/jeremyevans/sequel/pull/583
>
> Hope that helps.
>
> On Thursday, November 22, 2012 9:36:18 AM UTC-5, Rodrigo Rosenfeld Rosas
> wrote:
>>
>> Although I don't use ActiveRecord as an ORM, I do use its migrations
>> framework for evolving my database schema through standalone_migrations.
>>
>> But I'm finding the current DSL limited with regards to deferrable
>> constraints support.
>>
>> Let me demonstrate with a common use-case, like trees implementation,
>> with SQL written for PostgreSQL:
>>
>> create table tree(parent_id integer not null, position integer not null,
>> unique(parent_id, position));
>> insert into tree(parent_id, position) select generate_series(1, 3), 1;
>> insert into tree(parent_id, position) select generate_series(1, 3), 2;
>>
>> update tree set position = position + 1;
>>
>> ERROR: duplicate key value violates unique constraint
>> "tree_parent_id_position_key"
>>
>> In PostgreSQL the constraint check is performed during the UPDATE, so its
>> success will depend on what order PG will try to update the "position"
>> field.
>>
>> Since we want PG to defer the constraint check to the end of the UPDATE
>> statement (it is also possible to specify constraints to be check on
>> transaction commit, but I'll keep this short) we must create this "tree"
>> table in PG like this:
>>
>> create table tree(parent_id integer not null, position integer not null,
>> unique(parent_id, position) *deferrable*);
>>
>> But there is no DSL support in migrations to tell the database to use a
>> deferrable constraint when supported by the database.
>>
>> Also, I believe that deferrable should be the default and one should
>> specify "deferrable: false" if this is not desired in case add_index would
>> support a "deferrable" option. In such case, it should generate a statement
>> like:
>>
>> alter table :table_name add constraint :constraint_name unique
>> (parent_id, position) deferrable
>>
>> Another option would be to introduce some "add_constraint" DSL to
>> Migrations. Anyway, there should be some way of stating this deferrable
>> information in schema.rb.
>>
>> Each approach would be fine to me as long as I don't need to do it myself
>> using PG-specific SQL with "execute" method.
>>
>> Thanks for considering,
>> Rodrigo.
>>
>>
--
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Core" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/rubyonrails-core/-/UGqSpVNB5bEJ.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.
