Lack of foreign-key constraints support in ActiveRecord
Rodrigo Rosenfeld Rosas
I can understand the lack of support for triggers and stored procedures
since they are highly dependent on the database to create a wrap API
around them. But this is not the case for foreign keys.
This is specially important for open-source projects like
Redmine/Chiliproject and Gitorious where you shouldn't be assuming what
database is going to be chosen in the end point.
Besides that, the recommended way of creating a database is through rake
db:setup, which does that by reading the schema.rb DSL. Does this DSL
support foreign keys?
I think this is fundamentally wrong in ActiveRecord design and should be
considered as high-priority since foreign keys are a key concept in RDBMS.
I guess most Rails applications out there are doing this the wrong way
by not creating foreign keys at all, which is a huge mistake. This may
be helped by the fact that most Rails application chose MySql for some
reason and one of its dialect does not support foreign keys.
I'm really surprised that Rails still has some fundamental flaws like:
1 - the lack of foreign-key constraints support in schema.rb and
migration helpers;
2 - not considering the threaded model to deserve a first-class support;
3 - the lack of support for streaming
While 2 and 3 require a considerable effort to achieve, 1 is easily
achievable, by defining an API and DSL for schema.rb. Then, each
database maintainer could implement the API translating the proper SQL
statement for the foreign key specific methods.
Is there anything I'm missing?
Cheers,
Rodrigo.
Colin Law
> Is there any reason why AR has never supported foreign-key constraints?
Can you explain what you mean by that? Exactly what support is missing in AR?
Colin
Wael M. Nasreddine
Colin
--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group.
To post to this group, send email to rubyonra...@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-co...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
Waêl Nasreddine
TechnoGate www.technogate.fr
mobile : 06.41.68.38.35
agence : 09.70.444.236
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Colin Law
>
>
> On Mon, Nov 28, 2011 at 15:31, Colin Law <cla...@googlemail.com> wrote:
>>
>> On 28 November 2011 14:15, Rodrigo Rosenfeld Rosas <rr.r...@gmail.com>
>> wrote:
>> > Is there any reason why AR has never supported foreign-key constraints?
>>
>> Can you explain what you mean by that? Exactly what support is missing in
>> AR?
>>
>
> See https://github.com/matthuhiggins/foreigner to understand what he means
It was forgetting that migrations are part of ActiveRecord, I was
thinking of them as separate, but you are right, they are missing from
Migrations and therefore are missing from AR.
Colin
Rodrigo Rosenfeld Rosas
So, now it is a matter of discussing merging such an API into the Rails core. Possibly the entire gem could be integrated to Rails.
Or Rails could just add the tests to its core and use that gem for making the tests pass.
What about this idea?
James B. Byrne
On Mon, November 28, 2011 09:15, Rodrigo Rosenfeld Rosas
wrote:
> Is there any reason why AR has never supported foreign-key
> constraints?
>
Historically, RoR as opinionated software, viewed the
backing store as a bit-bucket and little else. The AR
Model was to provide the business logic. Database level
functions beyond store and retrieval were considered
superfluous in most respects. To put it bluntly, DHH was
not a DBA type of guy.
So, for example, things like primary key enforcement was
expected to be done through validations rather than
through the DBMS itself (with the predictable negative
experiences resulting from race conditions). Mostly, DBMS
generated errors were left to each application to handle
idiosyncratically rather than integrated into RoR's AR
validations scheme.
No doubt the effort required to handle the multitude of
errors generated by various DBMS adapters was considered
to be significantly greater than any benefits thereby
obtained for most RoR projects. In any case, people that
felt the need for such things had other ways to provide
them. RoR did not prevent using the DBMS to manage
consistency, it just did not make it as easy to do as it
did for other things.
Foreign key support for migrations was/is available only
through third-party extensions such as Red Hill plugin and
the Foreigner gem. But, such support was available from a
very early stage in RoR.
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:Byr...@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
Rodrigo Rosenfeld Rosas
> On Mon, November 28, 2011 09:15, Rodrigo Rosenfeld Rosas
> wrote:
>> Is there any reason why AR has never supported foreign-key
>> constraints?
>>
>
> Foreign key support for migrations was/is available only
> through third-party extensions such as Red Hill plugin and
> the Foreigner gem. But, such support was available from a
> very early stage in RoR.
By the way, I've just updated the guides:
https://github.com/lifo/docrails/commit/6d05c793cafe79860bcbb469d6c46c83c531ab34
Please, read the commit message.
Mislav Marohnić
SQLite hasn't had foreign key constraints until late 2009 and MySQL only has it on InnoDB tables (not default). So, for the most of Rails' lifetime, FKC support in supported databases wasn't ubiquitous. When you think about it, it makes sense that AR chose to implement these checks in the application (via validations) rather than relying on RDBMS.
The only big feature of relational systems that I can recall AR connected to is stored procedures, and that is Postgres-only if I remember. It had benefits in form of significant speedups. What significant gain would there be from relying on FKC?
Rodrigo Rosenfeld Rosas
> There are 3 main relational databases that Rails devs care about: SQLite, MySQL and Postgres.
>
> SQLite hasn't had foreign key constraints until late 2009 and MySQL only has it on InnoDB tables (not default). So, for the most of Rails' lifetime, FKC support in supported databases wasn't ubiquitous. When you think about it, it makes sense that AR chose to implement these checks in the application (via validations) rather than relying on RDBMS.
It should not be a matter of validations vs foreign keys. Both should be
used. The validations will help dealing with some cases in a
user-friendly way.
Foreign keys, in the other side, enforces constraints in the database
level. It means that if your sysadmin needs to do some manual procedures
in the database, it won't leave it in an inconsistent state. Neither
another application using the same database but through a different ORM
solution or by using direct SQL statements.
> The only big feature of relational systems that I can recall AR connected to is stored procedures, and that is Postgres-only if I remember. It had benefits in form of significant speedups. What significant gain would there be from relying on FKC?
It would also prevent incorrect state to happen in the presence of some
bugs in the validation side.
It also helps documenting the database.
I mean, there is no excuse for not adding the foreign key in databases
where they are supported. While on the subject, I can't really
understand why MySql has so much popularity. It doesn't even support
foreign keys by default!
Rails should add the feature to the Migrations framework regardless of
it being supported or not by the database. In the case the database
doesn't support it, it should simply create an index and it should be
left to the developers the decision of using a good database like
PostgreSQL or a bad one like MySql (Sqlite3 is being used only for
development - or at least, it should, so it shouldn't matter that much).
But specially, as James Byrne has already pointed out, it leads to
inconsistency in the databases even if there is no bug in the
validations code due to race conditions.
If some user asks some data to be removed while another asked for some
item to be associated with the same data being removed, it could happen
that the created item would point out to an item that does not exist
anymore.
So, let me ask you the opposite. Why not supporting FKC?
James B. Byrne
On Mon, November 28, 2011 12:02, Rodrigo Rosenfeld Rosas
wrote:
>
> So, let me ask you the opposite. Why not supporting FKC?
>
Define, precisely, what you mean by support. Is it just
the ability to set a foreign key constraint in the AR
migration? Or, do you desire more pervasive support for
Fks in AR? If so then what would this support comprise?
If the prevailing RoR application type is a mostly read
only environment, with few inserts having limited cross
referencing and fewer still updates, then exactly what
benefits would integral AR foreign key support provide to
the typical RoR application user?
In my own case, since I always use PostgreSQL and since
the Red Hill plugin to add Fk support to AR in has been
around almost from the outset, I have never had to do
without Fk support in RoR. Basically, if you know that you
need Fk support in an RoR application then you can get it.
And if you do not know that you need it then you will not
use it anyway.
Note, I am not arguing against including Fk support in AR.
I have long considered its absence a real defect. But I
certainly appreciate and respect the core team's p.o.v. on
the subject given the number of backend options they must
consider if they do decide to include it as a standard
feature. One has to conserve resources for work on the
essentials before the nice-to-haves are addressed.
Rodrigo Rosenfeld Rosas
> On Mon, November 28, 2011 12:02, Rodrigo Rosenfeld Rosas
> wrote:
>
>> So, let me ask you the opposite. Why not supporting FKC?
>>
>
> Define, precisely, what you mean by support. Is it just
> the ability to set a foreign key constraint in the AR
> migration? Or, do you desire more pervasive support for
> Fks in AR? If so then what would this support comprise?
I'll state the exact description in the end of this message.
> If the prevailing RoR application type is a mostly read
> only environment, with few inserts having limited cross
> referencing and fewer still updates, then exactly what
> benefits would integral AR foreign key support provide to
> the typical RoR application user?
I've already listed the benefits in my prior mail and I won't repeat them.
> In my own case, since I always use PostgreSQL and since
> the Red Hill plugin to add Fk support to AR in has been
> around almost from the outset, I have never had to do
> without Fk support in RoR. Basically, if you know that you
> need Fk support in an RoR application then you can get it.
My first message answers this:
"This is specially important for open-source projects like
Redmine/Chiliproject and Gitorious where you shouldn't be assuming what
database is going to be chosen in the end point"
"...foreign keys are a key concept in RDBMS"
I don't think key concepts to RDBMS (and AR ORM is supposed to be
developed with RDBMS in mind) should be delegated to plugins.
> And if you do not know that you need it then you will not use it anyway.
This is not true. There are lots of web frameworks out there where the
relationships are defined in the models and the database is generated by
the model with the FK being created out of the box in the supported
databases.
While I think such approach, taken by Grails ORM for instance, is
fundamentally wrong and migrations are definitely the way to go, I still
believe FK should be created in some situations when possible even if
the user has no idea about it.
This is specifically what I am talking about:
some migration snippet:
t.references :author
This should not only create the author_id numeric column, but also
create the foreign key in supported databases. For unprofessional
databases it should do the best it can, like creating an index if it is
possible, or even a trigger for simulating the foreign key restriction
if enabled by some configuration. Also if creating the foreign key won't
automatically create an index to it for some databases, such statement
should also take care of creating the index too.
> Note, I am not arguing against including Fk support in AR.
> I have long considered its absence a real defect. But I
> certainly appreciate and respect the core team's p.o.v. on
> the subject given the number of backend options they must
> consider if they do decide to include it as a standard
> feature. One has to conserve resources for work on the
> essentials before the nice-to-haves are addressed.
Exactly. But the issue here is that I consider this essential while some
do not.
Now, as promised, let me layout exactly what I was talking about. I'll
also borrow the method names from the foreigner gem.
1- I'm proposing the change to the "reference" type in the migrations,
like described above.
2- Add add_foreign_key and remove_foreign_key to the DSL syntax
3- Add some kind of foreign key representation to the schema.rb DSL
4- Detect foreign keys when possible on db:schema:dump
Of course, 4 is optional and could be done as a low-priority issue.
I hope this clarifies what I'm asking for.
Cheers,
Rodrigo.
Mislav Marohnić
t.references :author
This should not only create the author_id numeric column, but also create the foreign key in supported databases.
Mislav Marohnić
Eventual referential integrity as a result of race conditions was never a problem for me
Rodrigo Rosenfeld Rosas
> ...
> If there's a 3rd-party project (Foreigner), why not just use it?
Basically most things in any framework could be delegated to 3rd party
libraries.
What is the policy for defining when something should be in the core or
not in the case of Rails?
I've already stated that I think that FK is a key concept in RDBMS and
that AR is supposed to be an ORM for RDMBS databases.
That is the reason I think it should be integrated into core.
At the same time I have already proposed that only the tests could be
added to the AR code base while it could currently rely on foreigner for
making them pass.
Sorry for writing "too" long messages, but I want to be as clear as
possible exactly for avoiding repeating myself over and over again on
each new message. Unfortunately that doesn't seem to be working as
expected... :(
James B. Byrne
On Mon, November 28, 2011 13:50, Rodrigo Rosenfeld Rosas
wrote:
>
> Exactly. But the issue here is that I consider this
> essential while some do not.
Most do not, I would say from observing Rails related
discussions for the past 6 or 7 years.
I am not trying to dissuade you but, if you want this done
then you must provide the resources to accomplish what you
seek. I refer you to:
http://edgeguides.rubyonrails.org/contributing_to_ruby_on_rails.html
See - Section 1.3
And even then, with code in hand and tests galore, you
need convince the core team to accept the responsibility
contingent on incorporating it into AR since ongoing
maintenance is a serious concern. But until there is code
to consider I doubt that just discussing it will advance
your desire.
Rodrigo Rosenfeld Rosas
> On Mon, November 28, 2011 13:50, Rodrigo Rosenfeld Rosas
> wrote:
>> Exactly. But the issue here is that I consider this
>> essential while some do not.
> Most do not, I would say from observing Rails related
> discussions for the past 6 or 7 years.
>
> I am not trying to dissuade you but, if you want this done
> then you must provide the resources to accomplish what you
> seek. I refer you to:
>
> http://edgeguides.rubyonrails.org/contributing_to_ruby_on_rails.html
>
> See - Section 1.3
>
> And even then, with code in hand and tests galore, you
> need convince the core team to accept the responsibility
> contingent on incorporating it into AR since ongoing
> maintenance is a serious concern. But until there is code
> to consider I doubt that just discussing it will advance
> your desire.
Yes, I agree with you, so I'm officially resigning from this, once I'm
not working with Rails yet and have already wasted a lot of time on this
a while ago:
Aaron Patterson
> Now, as promised, let me layout exactly what I was talking about.
> I'll also borrow the method names from the foreigner gem.
>
> 1- I'm proposing the change to the "reference" type in the
> migrations, like described above.
> 2- Add add_foreign_key and remove_foreign_key to the DSL syntax
> 3- Add some kind of foreign key representation to the schema.rb DSL
> 4- Detect foreign keys when possible on db:schema:dump
>
> Of course, 4 is optional and could be done as a low-priority issue.
>
> I hope this clarifies what I'm asking for.
AFAIK, nobody has sent a pull request with these features. Adding FK
support is on my list, but I only have so much time in one day. If
someone does the work, I'll be happy to merge.
--
Aaron Patterson
http://tenderlovemaking.com/
Deryl R. Doucette
> it being supported or not by the database. In the case the database
> doesn't support it, it should simply create an index and it should be
> left to the developers the decision of using a good database like
> PostgreSQL or a bad one like MySql (Sqlite3 is being used only for
> development - or at least, it should, so it shouldn't matter that much).
>
No way. If you're going to add support for one database you add it for
all.
Just because *you* don't use one or the other does not mean you should
do half assed work. Either you support each and every database
equally,'
or you do nothing. Ruby and Rails's support has always been that the
db
on the backend is relegated to basically insignificance, so you can
concentrate
on coding rather than the configuration. Stick to it.
James B. Byrne
On Tue, November 29, 2011 09:09, Deryl R. Doucette wrote:
>
> No way. If you're going to add support for one database
> you add it for all.
Provided that we understand that 'all' in this case is
limited to the four standard adapters provided by AR,
which are really only three ( PostgreSQL, MySQL and
SQLite3 ).
matthuhiggins
Rodrigo Rosenfeld Rosas
I don't think this is right. A foreign key could be null. If you don't
want it to be null you should be explicit about it.
By the way, thanks for the foreigner gem.
Luiz Felipe Garcia Pereira
--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com.
Luiz Felipe G. Pereira
James B. Byrne
On Fri, December 2, 2011 06:32, Rodrigo Rosenfeld Rosas
wrote:
I do not think that is what the OP means. I believe that
he is trying to say that implementing a non-cascading Fk
at the DBMS level can be considered a 'backstop' to any
application logic in (or missing from) the model. This
may be considered similar to how the NOT NULL constraint
at the DBMS level is presently used inside AR. It just
catches logic errors before they hit the DB.
Rodrigo Rosenfeld Rosas
> On Fri, December 2, 2011 06:32, Rodrigo Rosenfeld Rosas
> wrote:
>> Em 01-12-2011 23:28, matthuhiggins escreveu:
>>> it should be noted that the update I offered has zero
>>> effect on those not using foreign keys. It's also worth
>>> suggesting that the initial version can start with no
>>> cascading options, so that all application logic is
>>> kept in the application, and the foreign keys
>>> act as a neutral constraint similar to NOT NULL.
>> I don't think this is right. A foreign key could be null.
>> If you don't want it to be null you should be explicit
>> about it
> he is trying to say that implementing a non-cascading Fk
> at the DBMS level can be considered a 'backstop' to any
> application logic in (or missing from) the model. This
> may be considered similar to how the NOT NULL constraint
> at the DBMS level is presently used inside AR. It just
> catches logic errors before they hit the DB.
Sure James, sorry about that. I should have read it more carefully.
And I also think that we should be explicit about cascading too while
declaring a "reference" type in the migrations.
Even in the database level, cascading (null or delete) should not be
considered the default for foreign keys since this is very specific to
the use case.
Jon Jensen
wrote:
> Also if creating the foreign key won't
> automatically create an index to it for some databases, such statement
> should also take care of creating the index too.
Postgres does not automatically create indexes for foreign keys, and
for good reason. Consider a "categories" table with an id, name, and
several other columns. It has only 5 rows. Then you have an "items"
table with a million rows. It has a category_id, and you make it a
foreign key. Adding an index with such a low cardinality is a Bad
Idea™ because 1. it will likely never be used by the query planner 2.
it will always slow down all writes to that table.
Rodrigo Rosenfeld Rosas
> On Nov 28, 11:50 am, Rodrigo Rosenfeld Rosas<rr.ro...@gmail.com>
> wrote:
>> Also if creating the foreign key won't
>> automatically create an index to it for some databases, such statement
>> should also take care of creating the index too.
> Postgres does not automatically create indexes for foreign keys, and
> for good reason. Consider a "categories" table with an id, name, and
> several other columns. It has only 5 rows. Then you have an "items"
> table with a million rows. It has a category_id, and you make it a
> foreign key. Adding an index with such a low cardinality is a Bad
> it will always slow down all writes to that table.
Yes, I was referring exactly to PostgreSQL. Don't you think that for
most associations we would want an index?
I'm proposing creating one unless specified not to. For example:
t.references :category, create_index: false
Don't you think 'true' should be default for :create_index?
James B. Byrne
On Tue, December 6, 2011 05:28, Rodrigo Rosenfeld Rosas
wrote:
>
> Yes, I was referring exactly to PostgreSQL. Don't you
> think that for most associations we would want an index?
>
> I'm proposing creating one unless specified not to. For
> example:
>
> t.references :category, create_index: false
>
> Don't you think 'true' should be default for
> :create_index?
>
I would caution against adding such 'invisible'
'enhancements'. The more so given the wide variability of
DBMS implementations. Better I think to leave everything
having to do with foreign keys off by default and have the
DBA specify case by case exactly which features should be
turned on.
Foreign keys are one of those things that prompt religious
wars and I would rather have Rails avoid adopting one side
or the other as dogma.
Rodrigo Rosenfeld Rosas
> On Tue, December 6, 2011 05:28, Rodrigo Rosenfeld Rosas
> wrote:
>
>> Yes, I was referring exactly to PostgreSQL. Don't you
>> think that for most associations we would want an index?
>>
>> I'm proposing creating one unless specified not to. For
>> example:
>>
>> t.references :category, create_index: false
>>
>> Don't you think 'true' should be default for
>> :create_index?
>>
> I would caution against adding such 'invisible'
> 'enhancements'. The more so given the wide variability of
> DBMS implementations. Better I think to leave everything
> having to do with foreign keys off by default and have the
> DBA specify case by case exactly which features should be
> turned on.
>
> Foreign keys are one of those things that prompt religious
> wars and I would rather have Rails avoid adopting one side
> or the other as dogma.
You might be right. Anyway, I would be happy if I would have to pass
"create_index: true" manually for most foreign keys too.
matthuhiggins
Rodrigo Rosenfeld Rosas
> I would caution against the :create_index idea. Just call add_index
> beforehand. The big win is getting this into schema.rb, not saving a
That would be ok to me too. I don't really mind about those details...