CODEWITHSUNDEEP
sqlite
mikesol
mikesol

Reputation: 1197

sqlite CHECK constraint on multiple rows

If I have the table:

CREATE TABLE foo (a INTEGER, b INTEGER, c INTEGER, PRIMARY KEY (a, b));

Can I enforce a column constraint where for all a of the same value (1 for example), c can be NOT NULL maximum one time. For example:

a  b  c
__________
1  2  NULL
1  3  NULL
1  4  1
1  5  NULL
2  4  1
2  5  NULL

would be permissible because for all a that are 1, there is only one NON NULL c. However:

a  b  c
__________
1  2  2
1  3  NULL
1  4  1
1  5  NULL
2  4  1
2  5  NULL

would not be permissible as there are two NOT NULL values in c for an a of 1.

I've been reading over the documentation for CHECK but I'm not sure how to express this in SQLite or if this can be expressed...

Upvotes: 2

Views: 558

Answers (1)

CL.
CL.

Reputation: 180060

To check the other rows, you need a subquery. The documentation says:

The expression of a CHECK constraint may not contain a subquery.

You could do this with a trigger:

CREATE TRIGGER check_one_c_insert
BEFORE INSERT ON foo
FOR EACH ROW
WHEN NEW.c IS NOT NULL
BEGIN
  SELECT RAISE(FAIL, 'another row has non-NULL c')
  FROM foo
  WHERE a = NEW.a
    AND b = NEW.b
    AND c IS NOT NULL;
END;

(And a similar trigger for UPDATE.)

Upvotes: 4

Related Questions