Rails core - Nov 2011 - Lack of foreign-key constraints support in ActiveRecord

Is there any reason why AR has never supported foreign-key constraints?

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.

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

On 28 November 2011 14:15, Rodrigo Rosenfeld Rosas <rr.rosas@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?

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 rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

On Mon, Nov 28, 2011 at 15:31, Colin Law <clanlaw@googlemail.com> wrote:
> On 28 November 2011 14:15, Rodrigo Rosenfeld Rosas
<rr.rosas@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

Wael

> 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 rubyonrails-core@googlegroups.com.
> To unsubscribe from this group, send email to
> rubyonrails-core+unsubscribe@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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

On 28 November 2011 14:34, Wael M. Nasreddine <wael.nasreddine@gmail.com>
wrote:
>
>
> On Mon, Nov 28, 2011 at 15:31, Colin Law <clanlaw@googlemail.com>
wrote:
>>
>> On 28 November 2011 14:15, Rodrigo Rosenfeld Rosas
<rr.rosas@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

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

Great Wael! I didn''t know about this gem, thanks!

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?

Em 28-11-2011 12:34, Wael M. Nasreddine escreveu:
>
>
> On Mon, Nov 28, 2011 at 15:31, Colin Law <clanlaw@googlemail.com 
> <mailto:clanlaw@googlemail.com>> wrote:
>
>     On 28 November 2011 14:15, Rodrigo Rosenfeld Rosas
>     <rr.rosas@gmail.com <mailto:rr.rosas@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
>
> Wael
>
-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

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:ByrneJB@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

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

Em 28-11-2011 13:14, James B. Byrne escreveu:
> On Mon, November 28, 2011 09:15, Rodrigo Rosenfeld Rosas
> wrote:
>> Is there any reason why AR has never supported foreign-key
>> constraints?
>>
> Historically, ...
>
> 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.

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

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.

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?

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

Em 28-11-2011 14:01, Mislav Marohnić escreveu:
> 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?

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

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.


-- 
***          E-Mail is NOT a SECURE channel          ***
James B. Byrne                mailto:ByrneJB@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

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

Em 28-11-2011 16:20, James B. Byrne escreveu:
> 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.

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

On Nov 28, 2011, at 7:50 PM, Rodrigo Rosenfeld Rosas wrote:
> t.references  :author
> 
> This should not only create the author_id numeric column, but also create
the foreign key in supported databases.
Ah, I didn''t understand we''re only talking about migrations
here.

You write too long emails; your whole point can be summarized by what I quoted
above :)

Eventual referential integrity as a result of race conditions was never a
problem for me, i.e. it hasn''t been biting me. That''s why
I''m not super-excited about the prospect of this being in AR by
default. If someone is, speak up.

If there''s a 3rd-party project (Foreigner), why not just use it?

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

On Nov 28, 2011, at 8:04 PM, Mislav Marohnić wrote:
> Eventual referential integrity as a result of race conditions was never a
problem for me
That was supposed to be "non-integrity"

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

Em 28-11-2011 17:04, Mislav Marohnić escreveu:
> ...
> 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... :(

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

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.

-- 
***          E-Mail is NOT a SECURE channel          ***
James B. Byrne                mailto:ByrneJB@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

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

Em 28-11-2011 18:06, James B. Byrne escreveu:
> 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:

https://github.com/rails/rails/issues/655

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

On Mon, Nov 28, 2011 at 04:50:23PM -0200, Rodrigo Rosenfeld Rosas
wrote:
> 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/

> 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).
>
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.

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

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 ).


-- 
***          E-Mail is NOT a SECURE channel          ***
James B. Byrne                mailto:ByrneJB@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

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

Earlier this year, I had a discussion with Rails Core and they suggested I 
make a pull request. I did:
https://rails.lighthouseapp.com/projects/8994/tickets/4347-add-foreign-key-support-to-migrations-and-schemarb-dump

I did not receive any feedback on my original request, so I went on 
maintaining github.com/matthuhiggins/foreigner. I would happy to make an 
update to the patch, if it will be considered by the Core team. 

While it is fruitless to debate the value of foreign keys to opponents and 
proponents, 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.

-- 
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/-/30aJofPy1DIJ.
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

Em 01-12-2011 23:28, matthuhiggins escreveu:
> Earlier this year, I had a discussion with Rails Core and they 
> suggested I make a pull request. I did:
>
https://rails.lighthouseapp.com/projects/8994/tickets/4347-add-foreign-key-support-to-migrations-and-schemarb-dump
>
> I did not receive any feedback on my original request, so I went on 
> maintaining github.com/matthuhiggins/foreigner. I would happy to make 
> an update to the patch, if it will be considered by the Core team.
>
> While it is fruitless to debate the value of foreign keys to opponents 
> and proponents, 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.

By the way, thanks for the foreigner gem.

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

Just make it optional, changing the default in application.rb. I agree with
no foreign keys by default, since it is what happens right now.
You can''t update rails and have all your migrations magically starting
to
add FKs.

On Fri, Dec 2, 2011 at 9:32 AM, Rodrigo Rosenfeld Rosas
<rr.rosas@gmail.com>wrote:
> Em 01-12-2011 23:28, matthuhiggins escreveu:
>
>  Earlier this year, I had a discussion with Rails Core and they suggested
>> I make a pull request. I did:
>> https://rails.lighthouseapp.**com/projects/8994/tickets/**
>>
4347-add-foreign-key-support-**to-migrations-and-schemarb-**dump<https://rails.lighthouseapp.com/projects/8994/tickets/4347-add-foreign-key-support-to-migrations-and-schemarb-dump>
>>
>> I did not receive any feedback on my original request, so I went on
>> maintaining
github.com/matthuhiggins/**foreigner<http://github.com/matthuhiggins/foreigner>.
>> I would happy to make an update to the patch, if it will be considered
by
>> the Core team.
>>
>> While it is fruitless to debate the value of foreign keys to opponents
>> and proponents, 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.
>
> By the way, thanks for the foreigner gem.
>
>
> --
> 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<rubyonrails-core@googlegroups.com>
> .
> To unsubscribe from this group, send email to
>
rubyonrails-core+unsubscribe@**googlegroups.com<rubyonrails-core%2Bunsubscribe@googlegroups.com>
> .
> For more options, visit this group at http://groups.google.com/**
>
group/rubyonrails-core?hl=en<http://groups.google.com/group/rubyonrails-core?hl=en>
> .
>
>

-- 
Luiz Felipe G. 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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

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.
>
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.

-- 
***          E-Mail is NOT a SECURE channel          ***
James B. Byrne                mailto:ByrneJB@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

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

Em 02-12-2011 12:47, James B. Byrne escreveu:
> 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
> 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.
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.

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

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
Idea™ because 1. it will likely never be used by the query planner 2.
it will always slow down all writes to that table.

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

Em 06-12-2011 03:31, Jon Jensen escreveu:
> 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
> Idea™ because 1. it will likely never be used by the query planner 2.
> 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?

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

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.

-- 
***          E-Mail is NOT a SECURE channel          ***
James B. Byrne                mailto:ByrneJB@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

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

Em 06-12-2011 13:23, James B. Byrne escreveu:
> 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.

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

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 few 
lines of migration code.

-- 
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/-/ZYdMI7QqK48J.
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

Em 06-12-2011 14:17, matthuhiggins escreveu:
> 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 
> few lines of migration code. --
That would be ok to me too. I don''t really mind about those details...

-- 
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.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.