DB Table Constraints using code or not! Best practices?

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.

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.