Joseph L. Casale wrote:
> CREATE TABLE t (
> id INTEGER NOT NULL,
> a VARCHAR NOT NULL COLLATE 'nocase',
> b VARCHAR COLLATE 'nocase',
> c VARCHAR CHECK (c IN ('foo', 'bar', NULL)) COLLATE 'nocase',
> PRIMARY KEY (id)
> );
>
> How does one elegantly construct an index or constraint such that for any
> row, column a may appear twice
You cannot look at other records without a subquery, which is not
allowed in CHECK constraints. You have to write a trigger instead.
And when you say "column a", do you actually mean "each distinct value
in column a"?
> ... with column c having a value of 'foo' and 'bar', unless this value
> for column a appears with a null value in column c where no other rows
> may now exist for that value of column a.
In other words, for each record, there must not exist any other record
with the same value in column a and a value in column c that is either
equal or NULL?
If I have decoded correctly what you were trying to say, use a trigger
like this, and duplicate it for UPDATE:
CREATE TRIGGER t_c_check_insert
AFTER INSERT ON r
FOR EACH ROW
BEGIN
SELECT RAISE(FAIL, '...')
FROM t
WHERE a = NEW.a
AND (c = NEW.c OR c IS NULL)
AND id <> NEW.id;
END;
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
