How to define a check constraint?

General Discussion
1

Let me see,

relational atabases that <span databases do not support this?

Oracle, SQL Server, DB2 and PostgreSQL alone are enough to justify such a feature. You do support sequences and they are supported by a smaller number of databases than check constraints.

This is very disappointing


2

Hi,

I’m trying out Liquibase, but I can’t figure out how to add a check constraint for a table.

In SQL I would do the following:

  1. CREATE TABLE foo
  2. (
  3.    some_pk integer not null primary key,
  4.    some_value integer not null,
  5.    other_value integer not null,
  6.    constraint some_greater_other check (some_value > other_value)
  7. )
or for just a single column
  1. constraint positive_salary check (salary > 0)
I looked through the manual, but apart from the possibility of using a custom SQL tag I could not find anything related to this.

I can’t believe that all other constraints are included by check constraints aren’t…

3

Many databases don’t have check constraints, whereas nearly all have unique and foreign key constraints.  So there isn’t a tag for them.  So that’s why they aren’t included.  Moreover, the syntax for a check constraint varies, and is not much different from custom sql anyway.

So your preferred route in this case would be to use a custom sql tag in a subsequent changeset.

I hope this helps.

Best,
Laird

4

I apologize that I seem to have offended you; I hope you have a good day.

Best,
Laird

5

So is there any plan to include that in the future?


6

I guess no answer means, it’s not planned, even though it’s supported by the vast majority of DBMS

7

Hello; it could just mean that Nathan is on vacation (the only guy who could really answer your question definitively).

Best,
Laird

8

It is something that I would like to get implemented, but have not had the chance to look into enough to determine how it would be implemented.  The trouble is that even though check constraints are implemented by many databases, their syntax can vary wildly, especially with the complex logic that is usually involved with check constraints.


For now, you can define them using blocks (if you want to use the normal XML create table, you can do that and add the constraint using after the fact) so they are still usable, just not as cross-database as one would maybe like.  With 2.0, you can also add custom attributes so if you have a particular check constraint need, you can implement support for defining them with easy syntax in your changelog file.


Nathan