Add support for table level check constraints

[dmcghan]

The /unique directive can be used at the column level or the table level (for multiple columns). However, the /check directive only works at the column level. It would be nice to see support for this directive at the table level for multiple columns as well.

Granted, a check constraint at the table level isn't going to be just a simple IN list, but a more complex expression. I'll log a separate issue for that specifically.

[vadim-tropashko]

By table level check constraints do you mean SQL Assertions, or something narrower in the scope?

[dmcghan]

No, not SQL Assertions. Here's an example that creates a table with two constraints: one column-level and one table-level. Table-level constraints can work with multiple columns.

create table t(
  c1 varchar2(10) check (c1 in ('A','B','C')),
  c2 varchar2(10),
  check (not (c1 = 'B' and c2 is null))
);

[vadim-tropashko]

In your particular example the constraint can be refactored into the two independent column-level constraints. However, I get the point, the suggested QSQL syntax should be something like this:

t /check not(c1 || c2 = 'A1' ) 
   c1 /check c1 in ('A','B','C')

[dmcghan]

In your particular example the constraint can be refactored into the two independent column-level constraints.

I don't believe this is true. You have to be able to check the values of two columns simultaneously.

suggested QSQL syntax should be something like this

Yes. An alternative would be something like /checkraw. "raw" meaning: we're not expecting an in list (as /check currently does). Instead, we'll take anything between /checkraw and the subsequent directive (if there is one) to be the expression.

[vadim-tropashko]

Implemented, see https://github.com/oracle/quicksql/blob/main/test/constraints.qsql