Support Table `CHECK` Constraints

[billy1624]

Discussed in https://github.com/SeaQL/sea-query/discussions/135

^{Originally posted by forrest-akin August 3, 2021} Currently, there's no way to specify table CHECK constraints in TableCreateStatements. Adding an .extra() method similar to ColumnDef would probably be the easiest way to add support for this.

[billy1624]

After some digging I found all three databases support CHECK constraint:

• SQLite: https://www.sqlite.org/lang_createtable.html#check_constraints
• MySQL: https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html
• PostgreSQL: https://www.postgresql.org/docs/current/ddl-constraints.html

[billy1624]

I'll open this issue for contributions

[nahuakang]

👋 Hey @billy1624! Happy to claim this if it's okay with you!

[ikrivosheev]

wave Hey @billy1624! Happy to claim this if it's okay with you!

Done!

[Razican]

@nahuakang is there any news with this?

[nahuakang]

@Razican Sorry I haven't had the time to got around to this issue. Are you interested in taking it? If so, please do :) I probably will only come back to this in 2-3 weeks earliest.

[LemarAb]

Id be happy to give it a go :)

[ikrivosheev]

@LemarAb hello! Any updates?)

[LemarAb]

@ikrivosheev I made some progress but need some time to deeper understand how sea query translates the stmt into SQL. Will report soon. If necessary, can I ask you for help?

[ikrivosheev]

@ikrivosheev I made some progress but need some time to deeper understand how sea query translates the stmt into SQL. Will report soon. If necessary, can I ask you for help?

Take your time! Yes, of course you can)

[LemarAb]

Just to clarify: CHECK constraints on Columns are already supported through the extra() method. We want to add CHECK constraints for the whole table, right? As an example for Postgres:

CREATE TABLE products (
    price integer CHECK(product_no > 0),
    discounted_price integer,
    CONSTRAINT valid_discount CHECK (product_no > discounted_price)
);

We want to add support for the second constraint, the first is already supported, correct?

@ikrivosheev

[ikrivosheev]

@LemarAb we want to add the ability to add CHECK constraint using SeaQuery, without extra method.

It will great if we can both)

[LemarAb]

@ikrivosheev ok, so afaik we need to add check() to ColumnDef and to table/create ?