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.

Can you explain what you mean by that? Exactly what support is missing in AR?

Colin

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

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

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?

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.

By the way, I've just updated the guides:

https://github.com/lifo/docrails/commit/6d05c793cafe79860bcbb469d6c46c83c531ab34

Please, read the commit message.

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?

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?

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.

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.

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 :slight_smile:

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?

That was supposed to be “non-integrity”

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

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:

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

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.

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.

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

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.