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 http://jitu-blog.blogspot.com/2009/04/use-migration-helpers-to-ease-your.html.

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