Ecto: CHECK and CASCADE support

[zmoshansky]

The SQL syntax for CHECK and CASCADE across databases (Following from #697).

CHECK
MySQL:
LOL, doesn't support CHECK, just ignores it src

SQL Server / Oracle / MS Access / Postgres / SQLLITE : src src2

CREATE TABLE Persons
(P_Id int NOT NULL CHECK (P_Id>0),

or with complex statements
SQL Server / Oracle / MS Access / Postgres / SQLLITE

CREATE TABLE Persons
(P_Id int NOT NULL,
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes'))

ON [UPDATE, DELETE]
Options:

1. SET NULL
2. CASCADE
3. SET DEFAULT
4. NO ACTION (This is what happens by default)

Postgres / SQL Server / SQLLITE / MySQL src src src

CREATE TABLE cats
(....,
owner_id INTEGER REFERENCES owners(id) ON DELETE [OPTION],)

_note_ CASCADE ON UPDATE isn't often (ever*_) needed if using surrogate keys, which Ecto does by default if I'm not mistaken.
*_ if surrogate keys are immutable then they never update :) , which in most applications they will be treated so

[josevalim]

We can support a :on_delete option in references/1 in migration which can have three values: nothing (do nothing), nilify_all (SET NULL) or delete_all (CASCADE). Similar to what have been described in #655. @hubertlepicki would you like to tackle this one too? :D

[josevalim]

Thanks @zmoshansky for all the research. That's very appreciated! <3

[hubertlepicki]

sure, @josevalim but not today. With all the exciting Elixir stuff going on I forgot I need to do some paid work. ;) Can probably add that tomorrow (Saturday) morning.

[josevalim]

No problem!

[hubertlepicki]

@josevalim :nothing should be default, right?

[josevalim]

Yes. Which means we don't need to even generate anything. You should get back to work though. :)

[hubertlepicki]

I have an hour until my US client wakes up. Maybe that will be enough!

[hubertlepicki]

@josevalim and nilify_all is a typo and in fact should be nullify_all or that's intentional?

[josevalim]

It is nilify_all according to #655!

[aerosol]

Hey, sorry for digging out the old ticket -- was the idea of supporting CHECK constraints abandoned?

[josevalim]

It is on master and it will be part of 2.0. You can check the CHANGELOG for more info. :)

[aerosol]

Whoa, lovely, thanks!

[davidkuhta]

From CHANGELOG:

[Postgres] Add migration and changeset support for PostgreSQL check constraints. Example: create constraint(:products, "positive_price", check: "price > 0") and check_constraint(changeset, :price, name: :positive_price, message: "must be greater than zero")

And for others who may have searched github issues before the docs...
Ecto.Changeset.check_constraint/3
Ecto.Migration.constraint/3