# DB Table Constraints using code or not! Best practices? **URL:** \ **Category:** Rails\ **Created:** [November 11, 2009, 11:43pm UTC](https://www.ruby-forum.com/t/db-table-constraints-using-code-or-not-best-practices/178840 "2009-11-11T23:43:06Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![bzouchir](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b5ac83/32.png) [@bzouchir](https://www.ruby-forum.com/u/bzouchir)\ **Post date:** [November 11, 2009, 11:43pm UTC](https://www.ruby-forum.com/t/db-table-constraints-using-code-or-not-best-practices/178840/1 "2009-11-11T23:43:06Z")
Hi guys, although this is not Rails specific, I have built this Rails app and designed the MySQL database with all constraints and indexes using MySQL Workbench. It is a pretty tool that allows to you to quickly build tables, create associations etc… Of course this means no use of migration, which is already bad practice per Rails I assume. And I know you can create those constraints in code through ActiveRecord associations attribute :dependent None of the tutorials mention any table constraints or indexing when building migrations: 1. Is that even possible to set in migrations? 2. Should we build those tables constraints and foreign keys straight in the Database? Or just simply keep it all loose and use :dependent instead, to cascade updates and deletes? What are the best practices in Rails for that matter? To answer FAQs: - Depends what you want to do: I want a clean table structure for my app, where I have dependencies with many tables. I need to cascade deletes and updates. - You can do them in both ways, build direct constraints or use ActiveRecord :depedent Yes I know, but what are the best practices? how to you guys do it and why? scalability is one of the aims as well. I don’t want Constraints on the tables to be in the way later. Please let me know from your experience. Thanks ---
**Author:** ![bzouchir](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b5ac83/32.png) [@bzouchir](https://www.ruby-forum.com/u/bzouchir)\ **Post date:** [November 12, 2009, 12:07am UTC](https://www.ruby-forum.com/t/db-table-constraints-using-code-or-not-best-practices/178840/2 "2009-11-12T00:07:55Z")
> ActiveRecord > the Database? Or just simply keep it all loose and use :dependent > ActiveRecord :depedent > Yes I know, but what are the best practices? how to you guys do it > and > why? scalability is one of the aims as well. I don’t want > Constraints on > the tables to be in the way later. > > Please let me know from your experience. Should be an interesting thread… Yes, you can set those things in your migrations… if Rails doesn’t support your DB natively you can always just write out the SQL directly. As for where to handle cascaded deletes… as long as it’s crystal clear to your team which way you’re going to roll you’ll probably be fine either way. That said… will there ever be a time where you want _more_ than a simple cascaded delete? Where you need to instantiate and destroy the object? Perhaps because that object has data stored in the file system or on S3? Or because you have counters somewhere that need to be decremented appropriately? Or maybe you have business logic such that you can’t delete an object until it’s children have been reassigned. Are you willing to dive into DB specific logic to make that work? If your app is pure Rails and will only ever be that way, I’d be inclined to keep things within Rails to keep the database a bit more agnostic, to help keep code in one place, and so you can pick up some of the Rails association magic if you want it. However, if your app will also be accessed from other systems and you need a central place to enforce rules, then the database has to have it. Not really an answer one way or the other… just some things to consider. -philip ---
**Author:** ![bzouchir](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b5ac83/32.png) [@bzouchir](https://www.ruby-forum.com/u/bzouchir)\ **Post date:** [November 12, 2009, 12:26am UTC](https://www.ruby-forum.com/t/db-table-constraints-using-code-or-not-best-practices/178840/3 "2009-11-12T00:26:04Z")
On Wed, Nov 11, 2009 at 2:43 PM, Bachir El khoury [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > the Database? Or just simply keep it all loose and use :dependent > ActiveRecord :depedent > Yes I know, but what are the best practices? how to you guys do it and > why? scalability is one of the aims as well. I don’t want Constraints on > the tables to be in the way later. > > Please let me know from your experience. FWIW, I use the foreign\_key\_migrations plugin which defines FK’s in my migrations for me. I do this because: 1. I don’t trust ActiveRecord to ensure my relations are sane 2. I don’t always use ActiveRecord to interact with my DB – Aaron T. [http://synfin.net/](http://synfin.net/) [http://tcpreplay.synfin.net/](http://tcpreplay.synfin.net/) - Pcap editing and replay tools for Unix & Windows Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. – Benjamin Franklin “carpe diem quam minimum credula postero” ---
**Author:** ![bzouchir](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b5ac83/32.png) [@bzouchir](https://www.ruby-forum.com/u/bzouchir)\ **Post date:** [November 12, 2009, 3:09pm UTC](https://www.ruby-forum.com/t/db-table-constraints-using-code-or-not-best-practices/178840/4 "2009-11-12T15:09:40Z")
Aaron T. wrote: FWIW, I use the foreign\_key\_migrations plugin which defines FK’s in my migrations for me. I do this because: 1. I don’t trust ActiveRecord to ensure my relations are sane 2. I don’t always use ActiveRecord to interact with my DB Marnen Laibow-Koser wrote: I do likewise, for the same reasons. I understand there’s now a similar plugin called Foreigner, but I’ve never used it. * * * Add another +1 for the foreign\_key\_migrations plugin. If some other person, or some other application will ever connect to your database (happens all the time here at work), defend it at the db level. ---
**Author:** ![bzouchir](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b5ac83/32.png) [@bzouchir](https://www.ruby-forum.com/u/bzouchir)\ **Post date:** [November 13, 2009, 4:21am UTC](https://www.ruby-forum.com/t/db-table-constraints-using-code-or-not-best-practices/178840/5 "2009-11-13T04:21:58Z")
Thanks alot guys for your input and suggestions. Good points. After researching about PostgreSQL which I might be using for the project, it is full of features that the db server can handle and automate rather than rely on Rails. I will stick with the database relations then, it never fails but just thought of getting a different perspective. thanks again ---
**Author:** ![bzouchir](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b5ac83/32.png) [@bzouchir](https://www.ruby-forum.com/u/bzouchir)\ **Post date:** [November 12, 2009, 5:38am UTC](https://www.ruby-forum.com/t/db-table-constraints-using-code-or-not-best-practices/178840/6 "2009-11-12T05:38:41Z")
Aaron T. wrote: > On Wed, Nov 11, 2009 at 2:43 PM, Bachir El khoury > [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > > the Database? Or just simply keep it all loose and use :dependent > > ActiveRecord :depedent > > �Yes I know, but what are the best practices? how to you guys do it and > > why? scalability is one of the aims as well. I don’t want Constraints on > > the tables to be in the way later. > > > > Please let me know from your experience. > > FWIW, I use the foreign\_key\_migrations plugin which defines FK’s in my > migrations for me. I do this because: > > 1. I don’t trust ActiveRecord to ensure my relations are sane > 2. I don’t always use ActiveRecord to interact with my DB I do likewise, for the same reasons. I understand there’s now a similar plugin called Foreigner, but I’ve never used it. > – > Aaron T. > [http://synfin.net/](http://synfin.net/) > [http://tcpreplay.synfin.net/](http://tcpreplay.synfin.net/) - Pcap editing and replay tools for Unix & > Windows > Those who would give up essential Liberty, to purchase a little > temporary > Safety, deserve neither Liberty nor Safety. > – Benjamin Franklin > “carpe diem quam minimum credula postero” ## Best, Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid ---
**Author:** ![bzouchir](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b5ac83/32.png) [@bzouchir](https://www.ruby-forum.com/u/bzouchir)\ **Post date:** [November 13, 2009, 6:56am UTC](https://www.ruby-forum.com/t/db-table-constraints-using-code-or-not-best-practices/178840/7 "2009-11-13T06:56:20Z")
Bachir El khoury wrote: > Thanks alot guys for your input and suggestions. > > Good points. > > After researching about PostgreSQL which I might be using for the > project, Good choice. With PostgreSQL, you get referential integrity and a fast storage engine. > it is full of features that the db server can handle and > automate rather than rely on Rails. > > I will stick with the database relations then, it never fails but just > thought of getting a different perspective. You probably want to have the AR validations as well – it makes error handling a bit easier. > thanks again ## Best, Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid