7gTa7MYN

1-- Scenario: There should only exist one record for each distinct value for column A that has column B set to true. There can be any number of records for each distinct value for column A where column B is false.
2-- i.e. there can be only one active record (column B true) for each distinct value of column A
3
4DROP TABLE IF EXISTS test;
5
6CREATE TABLE test (
7  "a" CHARACTER VARYING(3) NOT NULL,
8  "b" BOOLEAN NOT NULL DEFAULT TRUE
9);
10
11INSERT INTO test (a,b) VALUES ('1', true);
12INSERT INTO test (a,b) VALUES ('1', false);
13INSERT INTO test (a,b) VALUES ('1', false);
14INSERT INTO test (a,b) VALUES ('1', true);
15
16SELECT (
17  CASE WHEN COUNT(*) = 4 THEN 'PASS - Inserted without error without constraint' ELSE 'FAIL - Did not insert' END
18) AS "result" FROM test;
19
20
21TRUNCATE TABLE test;
22
23CREATE UNIQUE INDEX ON test (a,b) WHERE b = true;
24
25INSERT INTO test (a,b) VALUES ('1', true);
26INSERT INTO test (a,b) VALUES ('1', false);
27INSERT INTO test (a,b) VALUES ('1', false);
28INSERT INTO test (a,b) VALUES ('1', true);
29
30SELECT (
31  CASE WHEN COUNT(*) = 4 THEN 'FAIL - Inserted without error with constraint' ELSE 'PASS - Did not insert with constraint' END
32) AS "result" FROM test;