Forum: Ruby on Rails Are references and Foreign keys the same thing?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
2927af2b9ddd2f3f34b316755b8f4dfb?d=identicon&s=25 comopasta Gr (dgj)
on 2008-10-11 11:53
Hi,

I'm getting confused, are references and foreign keys the same thing
really?

http://www.jonathansng.com/ruby-on-rails/foreign-k...

Is there a good example on FKs?
I'd like to see how it goes in practice, for example how deleting the
parent record would delete any existing records depending on it, and
also how to avoid that from occurring...

Cheers.
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2008-10-11 12:19
(Received via mailing list)
On Oct 11, 10:53 am, Pod Caster <rails-mailing-l...@andreas-s.net>
wrote:
> Hi,
>
> I'm getting confused, are references and foreign keys the same thing
> really?
>
Sort of. I suspect what you are actually wondering about are foreign
key constraints (where the database enforces that each foo_id column
does indeed contain the id of a row from the foos table.
Rails doesn't provide anything builtin for doing this, although there
are various plugins.
The documentation for your database should provide information on how
it handles foreign keys
Fred
2927af2b9ddd2f3f34b316755b8f4dfb?d=identicon&s=25 comopasta Gr (dgj)
on 2008-10-11 12:32
Hi, thanks Fred.

Yes you are right I'm after FK constraints. And I guess that a way to do
that is for example using this in a migration file:
execute "alter table user_images add constraint fk_wi_user foreign key
(user_id) references users(id)"

Regards
2927af2b9ddd2f3f34b316755b8f4dfb?d=identicon&s=25 comopasta Gr (dgj)
on 2008-10-11 22:22
Hi,

Yeah now I checked about foreign key constraints for MySql:
http://dev.mysql.com/doc/refman/5.0/en/innodb-fore...

I think I understand it a bit better now.
Using references or something like t.integer :project_id help creating
the relations between tables, and help with queries like SELECT * FROM
projects WHERE user_id = 22;

The foreign key constrains help using those keys to keep DB data
consistent for example. As I mentioned I was actually looking for ON
DELETE CASCADE so that was missing on my constraint definition above. It
should be:

execute "alter table tasks add constraint fk_projects foreign key
(project_id) references projects(id) ON DELETE CASCADE"

----

Writing it here to put my thought in order...
Please correct me if I'm still mixing up things.

Regards
E3ba60e3dcb813f8abcd7732350e74cf?d=identicon&s=25 Phillip Koebbe (pkoebbe)
on 2008-10-13 03:46
To make this easier to do, you might consider writing yourself a little
helper, such as

def foreign_key(foreign_table, foreign_column, primary_table,
primary_column = :id)
  execute "
    alter table #{foreign_table.to_s}
      add constraint fk_#{foreign_table.to_s}_#{foreign_column.to_s}
      foreign key (#{foreign_column.to_s}) references
#{primary_table.to_s} (#{primary_column.to_s})
  "
end

def delete_foreign_key(foreign_table, foreign_column)
  execute "alter table #{foreign_table.to_s} drop constraint
fk_#{foreign_table.to_s}_#{foreign_column.to_s}"
end

so you can use it like

foreign_key :addresses, :person_id, :people
delete_foreign_key :addresses, :person_id

Peace.
This topic is locked and can not be replied to.