Vihung Vihung - 2 months ago 8
SQL Question

How can I specify a list of allowable values in a LiquiBase constraint?

I have a table I am trying to create using liquibase.

In plain old SQL, I would write

CREATE TABLE foo_bar (
foo_bar_id varchar2(40) NOT NULL,
is_foo number(1) NOT NULL,
is_bar number(1) NOT NULL,
CHECK (is_foo IN (0, 1)),
CHECK (is_bar IN (0, 1)),
PRIMARY KEY(foo_bar_id)
);


What is the equivalent (of the
CHECK (is_foo IN (0, 1))
statement) in liquibase XML?

Answer

Liquibase currently does not support check constraints directly. You need to run a custom SQL to do that:

<createTable name="foo_bar">
  .... here is the table definition without the check constraint
</createTable>
<sql splitStatements="false">
  alter table foo_bar 
    add constraint check_is_foo CHECK (is_foo IN (0, 1))
</sql>
<sql splitStatements="false">
  alter table foo_bar 
    add constraint check_is_bar CHECK (is_bar IN (0, 1))
</sql>
Comments