Hi,
Maybe I’m missing/not-understanding here something, but why would I want
to use the foreigner plugin if rails supports foreign key ? does the
“has_many/one” and “belong_to” not enough to take care of all activities
related to foreign key
e.g. delete from the database all records dependent on the “parent”
being deleted ?
Thanks,
Dani
On 16 November 2010 10:00, Dani D. [email protected] wrote:
Hi,
Maybe I’m missing/not-understanding here something, but why would I want
to use the foreigner plugin if rails supports foreign key ? does the
“has_many/one” and “belong_to” not enough to take care of all activities
related to foreign key
e.g. delete from the database all records dependent on the “parent”
being deleted ?
As the readme for Foreigner at
GitHub - matthuhiggins/foreigner: Adds foreign key helpers to migrations and correctly dumps foreign keys to schema.rb states:
Foreigner introduces a few methods to your migrations for adding and
removing foreign key constraints.
Constraints being a key word here. It is for use in migrations, not
in the app itself.
Colin
Colin L. wrote in post #961785:
As the readme for Foreigner at
GitHub - matthuhiggins/foreigner: Adds foreign key helpers to migrations and correctly dumps foreign keys to schema.rb states:
Foreigner introduces a few methods to your migrations for adding and
removing foreign key constraints.Constraints being a key word here. It is for use in migrations, not
in the app itself.Colin
Ok, thank you, wasn’t sure.
So there is no need for any plugin to support ActiveRecord regarding
foreign key use, e.g. make sure that all dependent records to be removed
from the database ? (sorry if it sounds too basics).
Thanks,
Dani
On 16 November 2010 11:53, Dani D. [email protected] wrote:
Colin
Ok, thank you, wasn’t sure.
So there is no need for any plugin to support ActiveRecord regarding
foreign key use, e.g. make sure that all dependent records to be removed
from the database ? (sorry if it sounds too basics).
That is correct. Of course you still have to write the correct code.
In fact there is never any need for a plugin as anything in a plugin
can just be written by yourself. Plugins just allow us gratefully to
stand on the shoulders of those that have come before.
Colin
Colin
Dani D. wrote in post #961802:
Colin L. wrote in post #961785:
As the readme for Foreigner at
GitHub - matthuhiggins/foreigner: Adds foreign key helpers to migrations and correctly dumps foreign keys to schema.rb states:
Foreigner introduces a few methods to your migrations for adding and
removing foreign key constraints.Constraints being a key word here. It is for use in migrations, not
in the app itself.Colin
Ok, thank you, wasn’t sure.
So there is no need for any plugin to support ActiveRecord regarding
foreign key use, e.g. make sure that all dependent records to be removed
from the database ? (sorry if it sounds too basics).
There is absolutely a need for the plugin. AR can enforce a certain
amount of foreign key integrity in the application layer (including
deleting dependent records), but enforcing it in he application layer is
IMHO not really enforcing it at all: it only works insofar as the AR
interface is the only way the DB is ever touched.
And you really can’t guarantee that in most cases. Therefore, it is
absolutely essential to have foreign key constraints in the DB, which is
what Foreigner manages for you.
Thanks,
Dani
Best,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
Dani D. wrote in post #961839:
Marnen Laibow-Koser wrote in post #961826:
And you really can’t guarantee that in most cases. Therefore, it is
absolutely essential to have foreign key constraints in the DB, which is
what Foreigner manages for you.what exactly is ment here, where can I read about what and how should
these
“foreign key constraints in the DB” can be done ?
Thank you,
Dani
Best,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
Marnen Laibow-Koser wrote in post #961826:
And you really can’t guarantee that in most cases. Therefore, it is
absolutely essential to have foreign key constraints in the DB, which is
what Foreigner manages for you.
what exactly is ment here, where can I read about what and how should
these
“foreign key constraints in the DB” can be done ?
Thank you,
Dani
Thank you Marnen and Phoenix for your detailed explanations.
I do understand the functionality of the foreign key. I just was not
sure
whether relying on rails’s foreign key functionaliy is good enough and
there is no need to do it directly on the database, but now reading
Phoenix’s explanation, I understand that as long as only my rails
application is accessing the database, I can rely on rails’s foreign key
functionality. Of
Thank you again.
Dani
Basically, Rails uses the has_many/one belongs_to features to “mimic”
the functionality of a database foreign key. A real foreign key,
however, is built DIRECTLY into the database structure itself instead
of relying on Rails to make it work. This is useful in situations
where you have a Rails application, but other programs or programmers
connect directly to the same database to do work with it. This
happens a lot in corporate setups (data warehousing, etc.)
To illustrate, say you have two tables: users and orders. Every order
has a user_id field that “belongs_to” a user. So in Rails, we could
do:
order = Order.first
order.user # gives us the user
But what happens if we don’t go through Rails and instead connect
directly to the database underneath it? Furthermore, if we do that,
then try to save a row in the database without assigning a user_id to
it, or one that doesn’t exist, the Rails application (or any other
application that depends on the same database) could break because the
data would be written in a state that the applications using the
database don’t expect.
So, in other words, given the same setup, let’s say I do this (in
SQL):
INSERT INTO orders (‘user_id’, ‘order_total’) VALUES (9912929191999,
29.99);
What happens if there isn’t a user with the ID of 9912929191999?
Well, without a foreign key, this SQL query would execute just fine,
because the database doesn’t know that user_id is supposed to
correspond to the users table (SQL doesn’t really do “convention over
configuration”). Then later, when somebody tries to pull up that
order, the Rails application will probably throw an error 500,
depending on how things are set up, because it’ll try to find that
user, that doesn’t exist.
A database foreign key prevents this from happening because it’ll
first check to make sure that the user with the given ID (in this
example) actually exists before trying to write the row. So when
other programmers or applications access the same database as your
Rails app, if you have foreign keys properly set up, you’ll be able to
prevent those other programmers or applications from writing “bogus”
data.
The foreigner plugin/gem adds some methods to the Rails database
migrations to allow somebody to do this without resorting to direct
SQL (so you can build an application on one type of SQL database -
MySQL, PostgreSQL, etc., and deploy it on another - say Oracle or MS
SQL Server [neither recommended, but just used for example]). Since
Rails doesn’t build this directly into its own migrations system
(there isn’t really an ‘add_foreign_key’ method in Rails database
migrations without using this plugin), the foreigner plugin/gem adds
them for you so you can use them.
I hope this clarifies things for you. For some further reading, check
out the following:
http://articles.techrepublic.com.com/5100-10878_11-6035435.html
Phoenix R. wrote in post #962048:
Basically, Rails uses the has_many/one belongs_to features to “mimic”
the functionality of a database foreign key. A real foreign key,
however, is built DIRECTLY into the database structure itself instead
of relying on Rails to make it work.
Not quite. Your terminology is a little off. A foreign key is simply a
value in one table which refers to a key field in another. Rails’
associations use real foreign keys in this sense.
What they do not do, however, is set up foreign key constraints. I
think you’re confusion the idea of a foreign key with that of a foreign
key constraint.
This is useful in situations
where you have a Rails application, but other programs or programmers
connect directly to the same database to do work with it. This
happens a lot in corporate setups (data warehousing, etc.)
Or anytime you try to use a DB administration tool to tweak a value!
Best,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
Sent from my iPhone
Dani D. wrote in post #962064:
Thank you Marnen and Phoenix for your detailed explanations.
I do understand the functionality of the foreign key. I just was not
sure
whether relying on rails’s foreign key functionaliy is good enough and
there is no need to do it directly on the database, but now reading
Phoenix’s explanation, I understand that as long as only my rails
application is accessing the database, I can rely on rails’s foreign key
functionality. Of
But you can never guarantee that. In virtually every Rails application
I’ve worked on, I’ve needed to use a DB administration tool at some
point to tweak a value or check a field definition. In other words,
I’ve needed to use a non-Rails interface at some point, and therefore
I’ve needed the data integrity checks of a foreign key constraint.
Working without foreign key constraints is playing with fire. It may
look like it is working, but you’ll be setting yourself up for subtle
bugs that may not be immediately obvious but will cause you no end of
grief when they occur. Don’t ever do that.
Moral: if you care about the integrity of your data, you need physical
constraints in the DB to ensure that integrity. If you don’t care about
that integrity, don’t waste time storing the data. 
Thank you again.
Dani
Best,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
Sent from my iPhone
Dani D. wrote in post #962996:
Marnen Laibow-Koser wrote in post #962137:
Working without foreign key constraints is playing with fire. It may
look like it is working, but you’ll be setting yourself up for subtle
bugs that may not be immediately obvious but will cause you no end of
grief when they occur. Don’t ever do that.Moral: if you care about the integrity of your data, you need physical
constraints in the DB to ensure that integrity. If you don’t care about
that integrity, don’t waste time storing the data.Hi,
In reference to the above, I happen to come across the “Advanced Rails
Recipes” book from Mike C… Recipe Nr. 8 in the book is called “Add
Foreign Key Constraints”, explaining how to add foreign key constaraints
to the database to ensure referntial integrity using the ‘execute’
method. I assume this would then be sufficient to prevent manual doings
directly in the DB. Any experience with this ?.
Yes. It will work, but it will be unmaintainable (because the
constraints will not appear in the schema.rb file) and tied to one DB.
Don’t ever do it that way. Use Foreigner and be dome with it.
Regards,
Dani
Best,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
Sent from my iPhone
Marnen Laibow-Koser wrote in post #962137:
Working without foreign key constraints is playing with fire. It may
look like it is working, but you’ll be setting yourself up for subtle
bugs that may not be immediately obvious but will cause you no end of
grief when they occur. Don’t ever do that.Moral: if you care about the integrity of your data, you need physical
constraints in the DB to ensure that integrity. If you don’t care about
that integrity, don’t waste time storing the data.
Hi,
In reference to the above, I happen to come across the “Advanced Rails
Recipes” book from Mike C… Recipe Nr. 8 in the book is called “Add
Foreign Key Constraints”, explaining how to add foreign key constaraints
to the database to ensure referntial integrity using the ‘execute’
method. I assume this would then be sufficient to prevent manual doings
directly in the DB. Any experience with this ?.
Regards,
Dani
Hey Dani,
I haven’t actually read any of the Rails recipes books, so I can’t
comment on this exactly, but I think I get what you’re saying/asking.
When you use ActiveRecord, the database abstraction and modeling
system in Rails, it has a method called “execute” that you can call
by:
ActiveRecord::Base.connection.execute(“your sql statement here”)
The thing is that you have to supply a FULL SQL statement to the
execute method. This is the exact same thing as connecting to the
database directly and issuing the statement “by hand”. The problem is
that doing this will require you to write the SQL statement in
whatever syntax your particular database of choice is using. So if
you’re developing on MySQL and you want to deploy using PostgreSQL,
for example, you’ll end up writing your SQL statement inside the
execute method using a MySQL “flavored” statement that may not work
later on if you deploy using PostgreSQL.
The other problem is that when you deploy an application in
production, while you CAN use database migrations to create the data
structure, it’s generally not recommended because it can be a lot
slower than having Rails use its schema.rb file. Every time you run a
database migration, Rails automatically updates this file (schema.rb)
to reflect the CURRENT state of your database’s structure. This way,
when it’s time to deploy, you can just do rake db:setup
RAILS_ENV=production and Rails will read the schema.rb file and make
the database structure match what’s in it.
The problem is, as Marnen points out (I’m just elaborating here to
help you learn a bit more), that because Rails doesn’t have any built
in support for database level foreign keys, it’ll never put this
information in schema.rb. So when you go to deploy your application,
without foreigner, it won’t know to actually add the foreign keys
since it’s not running the migrations.
You might think, “well that’s simple, just deploy using rake db:create
and then rake db:migrate”. Even if you did it this way, you could
still run into problems if you wrote your SQL for adding the foreign
key based on the syntax of one database, and you’re deploying on
another.
This is why foreigner is so useful. It adds all the “awesomeness”
needed to make adding foreign keys database-agnostic (develop on one,
deploy on another) and adds this capability to schema.rb, and helps
Rails keep track of it all.
I hope this helps you wrap your head around it some more! Good luck!
Dani D. wrote in post #962996:
Marnen Laibow-Koser wrote in post #962137:
Working without foreign key constraints is playing with fire. It may
look like it is working, but you’ll be setting yourself up for subtle
bugs that may not be immediately obvious but will cause you no end of
grief when they occur. Don’t ever do that.Moral: if you care about the integrity of your data, you need physical
constraints in the DB to ensure that integrity. If you don’t care about
that integrity, don’t waste time storing the data.Hi,
In reference to the above, I happen to come across the “Advanced Rails
Recipes” book from Mike C… Recipe Nr. 8 in the book is called “Add
Foreign Key Constraints”, explaining how to add foreign key constaraints
to the database to ensure referntial integrity using the ‘execute’
method. I assume this would then be sufficient to prevent manual doings
directly in the DB. Any experience with this ?.
Yes. It will work, but it will be unmaintainable (because the
constraints will not appear in the schema.rb file) and tied to one DB.
Don’t ever do it that way. Use Foreigner and be done with it.
Regards,
Dani
Best,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
Sent from my iPhone