mardi 6 octobre 2015

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...

Aucun commentaire:

Enregistrer un commentaire