Foreign key constraints

When #belongs_to/#references was added to schema definitions I assumed without looking that this would add foreign key constraints to the database. Alas, I was wrong. Apparently I misunderestimated the existing reservations against database-level operations.

For a long time there have been plugins adding foreign key constraints, but as far as I can tell, most or all of them have gone unmaintained. Is there any interest and support by core developers to add this functionality to ActiveRecord once and for all? If there is, I might put in the effort; if there isn't, I won't waste my time.

Michael

I'm maintaining mine - http://github.com/willbryant/foreign_key_saver/tree/master - but it tends to not need any changes so it's been a long time since there's been commits. It supports all the update/delete actions, and knows how to dump out FKs in schemas so they get restored in your test db (etc.). I don't hook the newer association-like table DML methods as you've suggested, but yeah I can certainly see how that would be a useful feature for some people.

I only support mysql and postgresql in it, so will comment on how well FKs work in them specifically, which perhaps might influence how suitable FK support would be to have in core.

Postgresql generally I'm pretty happy with, extracting out the FKs is a little obtuse but really nothing to complain about.

Mysql works acceptably, but there is an annoying performance caveat, which is that Mysql does not cache the data dictionary stuff properly, and to get the info they need to scan through all the database table file directories on disk and open up each table definition, which is very slow (well, relatively speaking).

As a result if you have a database with a reasonable number of tables, you will find that, for example, dropping tables is now somewhat bit slower (1-5s, for the one customer project I'm working on at the moment which has ~200 tables) as this requires checking for FKs that need to be dropped first, which hits the data dictionary.

(They have improved the speed of these operations in later versions of Mysql, but not solved the problem - and given that the bug has been open for several years now, it seems they are reluctant to, apparently on the basis that they don't want to lose the support for the few odd users who want to be able to drop new database tables into the mysql data directories and have them immediately available without telling the server. Not my idea of a good engineering tradeoff, but there you go.)

I wouldn't personally think this blocks adding support to core, but it's something to bear in mind. You should also note that mysql does not support "delayed constraints" that are checked at transaction commit, which makes it impossible or difficult to make certain key changes, but since with AR basic key best practices such as not changing PKs are very strongly encouraged, I can't see anyone caring about that (and they could always just disable FKs or turn off whatever option gets added, for those weird tables, anyway).

Finally, as in my readme, mysql does not support "set default" or "restrict" on update/delete actions, so we would need to either go for the lowest common denominator and not offer those as options, or live with mysql not implementing all the options.

Anyway, re integration to core, I think that if we were going to add FK support, we would need to somehow support sqlite (Firebird and Oracle can be added fairly easily - I know of at least one other plugin that supports them without any drama, though I would definitely not recommend that plugin as at least of when I last looked at it as the authors had failed to mention that they have an unrelated monkeypatch in their plugin that changes how NULLs behave! naughty.)

I haven't attempted that (sqlite support), but their wiki explains that they support the syntax of FK declarations but that they have no effect; they suggest using a trigger to do this. If we could write a standard routine to deal with these, then I suppose that Rails could automatically create this/these routine(s) in the database when creating an FK, but I haven't tried it (would love to hear if you do).

Cheers, Will

I am also working / maintaining code that does foreign constraints, unique keys, etc.

I'm hoping to get it back stable after moving up to Rails 2.3.2 this weekend. I'll update this thread if I do.

I am also working / maintaining code that does foreign constraints, unique keys, etc.

Just wondering, does your code for uniqueness differ from unique constraints? As far as I'm aware, defining a column as unique in SQL is just a syntactic shortcut for a unique index.

I'm hoping to get it back stable after moving up to Rails 2.3.2 this weekend. I'll update this thread if I do.

Great. I was about to start work on a patch based on Will's work and that of others plugin authors, but I'll hold back then.

My general idea is to support only MySQL and PostgreSQL in the first iteration in case the other DBMS require special. Also, as there may be reservations about this feature, I think there ought to be a global switch and an individual :check option on #references/#belongs_to. Beyond that, I'd like to support constraints on polymorphic associations where possible.

Michael

I'm doing specific PostgreSQL right now.

When the table is defined, I'm adding the SQL keywords like "UNIQUE" or "REFERENCES ...".

I'm also linking into the save routine. If you don't, a failed constraint throws an exception. But foo.save is suppose to return false -- not throw an exception. So I catch it. I rummage around Postgres' system tables translating the error into what happened and assign the proper strings to the foo.error.on fields so your view stuff should work. That is not going to translate to MySQL well at all.

The new Rails 2.3.2 with check points is key. Before 2.3.2 it was a real pain to do tests. Now my testing is pretty pain free. I only had to do the schema = :sql change so that my test db has all the constraints. Aside from that, I can now test my constraints and keep all the normal fixture speed improvements. (Oh... thats not true... you have to load your fixtures in a specific way but that is fairly easy too.)

Constraints on polymorphic associations will need to be a check condition. Its hard to do (well... not hard but there are a few tricks involved). I put Postgres constraints for polymorphic associations a few years ago (by hand). There is a post I made to the regular Rails forum. Right now, I don't have "check" but it should not be too hard.

> My general idea is to support only MySQL and PostgreSQL in the > first iteration in case the other DBMS require special. Also, as > there may be reservations about this feature, I think there ought > to be a global switch and an individual :check option on > #references/#belongs_to. Beyond that, I'd like to support > constraints on polymorphic associations where possible.

I'm doing specific PostgreSQL right now.

When the table is defined, I'm adding the SQL keywords like "UNIQUE" or "REFERENCES ...".

These are both SQL:1999 and, according to the docs, supported in MySQL 5.1.

I'm also linking into the save routine. If you don't, a failed constraint throws an exception. But foo.save is suppose to return false -- not throw an exception. So I catch it. I rummage around Postgres' system tables translating the error into what happened and assign the proper strings to the foo.error.on fields so your view stuff should work. That is not going to translate to MySQL well at all.

I'd be interested to see how you do this as I wrote a patch that translates violations of uniqueness constraints to an ActiveRecord::RecordNotUnique exception. See

https://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/2419

As was discussed there, I think it is dubious to translate exceptions to validation errors. For one thing, in the life-cycle of an object, (ordinary) validation happens before accessing the database. Consider this scenario:

- An object validates successfully. - The object is #save'd to the database, thereby triggering an exception. - You catch the exception, translate it to a validation error, and return false from #save. - Directly or mediated, you call #valid? on the object, which in turn re-validates it and resets all the errors induced by database exceptions.

In my opinion, exceptions bubbling up from the database are either programming errors or ought to be handled as exception. They are programming errors if NOT NULL or referential integrity constraints are violated. Uniqueness has to be checked by the database, but doesn't fit in with the usual place of validation in the life-cycle.

Michael

Finally got this out the door:

http://github.com/pedz/activerecord_constraints

feed back is welcomed.

I wouldn't personally think this blocks adding support to core, but it's something to bear in mind. You should also note that mysql does not support "delayed constraints" that are checked at transaction commit, which makes it impossible or difficult to make certain key changes, but since with AR basic key best practices such as not changing PKs are very strongly encouraged, I can't see anyone caring about that (and they could always just disable FKs or turn off whatever option gets added, for those weird tables, anyway).

Finally, as in my readme, mysql does not support "set default" or "restrict" on update/delete actions, so we would need to either go for the lowest common denominator and not offer those as options, or live with mysql not implementing all the options.

For those interested, Mysql will have a completely new foreign engine in 6.1 (replacing the innodb-specific foreign key engine currently available). It will support these additional actions, and by the looks of it will support statement-level foreign key checks although not deferred FK constraints (which is the thing discussed in the first paragraph above - but statement-level is good enough for most people), along with various other functionality fixes relative to SQL 2003.

More info in the presentation from: http://www.mysqlconf.com/mysql2009/public/schedule/detail/6814

My belief is that unique and foreign key constraints should be in the database. As the comments on validates_uniqueness_of says, you can't really do that outside of the database. The same is true for foreign keys if not more so. If you have a db engine that can not do them, then I wonder what you are really up to.

There are other constraints that need to be as close to the user as possible: in the javascript if appropriate, and in the application, and in the database. Examples of this is "not blank" or "not null" or particular format of a field.

My objective eventually with this plug in is to have one place to specify the constraints.

What I plan to do is to have a table. The migration puts the essence of the constraint in the table. When the application starts up, this essence is pulled out and made in to a Ruby validation as well as a javascript validation.

For the more robust databases, the migration will populate all three places where it makes sense and for the weaker databases, only in the application and javascript.

The one downside that I have not dealt with is the existing Rails code that puts the validates_... in the models. Hopefully, the interface in the migration will be easy to port these validates_... to.

You don't need to add more metadata tables to the DB as all the information is already there.

Have a look at

http://agilewebdevelopment.com/plugins/schema_validations http://agilewebdevelopment.com/plugins/foreign_key_migrations http://agilewebdevelopment.com/plugins/foreign_key_associations http://agilewebdevelopment.com/plugins/enforce_schema_rules http://agilewebdevelopment.com/plugins/validation_reflection http://agilewebdevelopment.com/plugins/client_side_validation (the last one for inspiration only)

Michael

I may have not been clear. I don't use other databases but I was under the impression that many of them (most of them) do not even have constraints. It was for those databases that I was going to add in the extra table. For PostgreSQL, as you said, that data is already there.

Rails do not create a foreign key reference in database when we reference a model in migration. But there are some plugins to create foreign key easily from your migration script. You can use this plugin to write insert and update command using ruby syntax from migration script. See Ashrafuzzaman on Technology: Use Migration Helpers to ease your migration script.

Jitu,

I think the plugin you're referring to as well as the technique you're demonstrating are broken. All database changes made with this plugin, structural and data, only affect the migrated database.

Writing new data to the DB from a migration has been discouraged for a long time. A migration is only meant to change the database structure and move the existing data along. Also, foreign key constraints added with the plugin you mention are not carried through to the schema.rb dump and therefore are not used in tests or any other databases constructed from the schema.rb

Michael