sequelize/sequelize — pull request #595 by optilude (2013-05-06 22:37:11+00:00)
Title: Support for foreign key constraints (#389)

This implements support for creating `FOREIGN KEY ... REFERENCES` declarations with `ON DELETE` and `ON UPDATE` triggers when defining associations, for all supported databases.

The basic syntax is:

var Task = this.sequelize.define('Task', { title: Sequelize.STRING })
, User = this.sequelize.define('User', { username: Sequelize.STRING })

User.hasMany(Task, {onDelete: 'restrict'})

The same works for `hasOne()` and `belongsTo()`. Valid options are:

* `onDelete` -- set to e.g. `restrict` or `cascade`
* `onUpdate` -- set to e.g. `restrict` or `cascade`
* `foreignKeyConstraint` -- set to `true` to gain a `REFERENCES` declaration without either `onDelete` or `onUpdate` (that is, `foreignKeyConstraint` is implied if `onDelete` or `onUpdate` are set). This may be a helpful performance optimisation in some cases.

Some key implementation notes:

* Enabling constraints is opt-in: they are only created if one or more of the options above are used when defining an association.

* MySQL (with InnoDB tables) and Postgres support foreign key references by default. SQLite does not unless `PRAGMA FOREIGN_KEYS=ON` is issued. We do so when opening the database connection, unless explicitly disabled with a global option, to get parity across implementations. Note that just enabling this doesn't make any difference unless there are actual constraints in place.

* Only simple foreign keys are supported (because associations only support simple keys). This is the "80%" use case of course. Setting one of foreign key options in a situation where there is more than one primary key defined will cause the option to be ignored.

* SQL allows two ways to define foreign keys: "inline" (`someId INTEGER REFERENCES someTable(id) ON DELETE CASCADE`) or "standalone" (`FOREIGN KEY(someId) REFERENCES someTable(id) ON DELETE CASCADE` placed as a separate clause inside a `CREATE TABLE` statement). Since associations in sequelize create foreign key attributes the "inline" syntax is used, and `attributesToSQL` is taught how to add the relevant suffix to any "raw" attribute with the relevant metadata attached. This works for Postgres and SQLite, but MySQL ignores this syntax, requiring the "standalone" approach. For MySQL, we move the declaration to the end with a bit of string manipulation. This is analogous to how `PRIMARY KEY` is handled and allows this to be done without major refactoring.

* If we have foreign key constraints, the order in which tables are created matters: if `foo` has a foreign key to `bar` with a constraint, then `bar` has to exist before `foo` can be created. To make sure this happens, we use a topological sort of relationships (via the `Toposort-class` module, added as a dependency) to sequence calls to `CREATE TABLE` in `sync()`. This also necessitates `sync()` being serialised, but given it's an "on startup" operation that shouldn't be too much of an issue.

* A similar concern happens with `dropAllTables()`, but here we don't have enough information to sort the list. Instead, we do one of two things: for SQLite and MySQL, we temporarily disable constraint checking. For Postgres, we use `DROP TABLE ... CASCADE` to drop relevant constraints when required. (MySQL and SQLite only support the former and Postgres only supports the latter). This is blunt, but OK given that the function is attempting to drop *all* the tables.

* For other calls to `dropTable()` the caller is expected to sequence calls appropriately, or wrap the call in `disableForeignKeyConstraints()` and `enableForeignKeyConstraints()` (MySQL and SQLite; no-ops in Postgres) and/or pass `{cascade: true}` in `options` (Postgres; no-op in MySQL and SQLite).

* There are (passing) tests of the various dialect `QueryGenerator`s and of cascade and restrict behaviour at the integration test level.

* I will add documentation (unless you'd prefer to do it yourself) if/once this PR is accepted, I just don't want to do it yet if there's going to be challenge.