Conditional constraints

CREATE TABLE products(
	id SERIAL PRIMARY KEY
);

CREATE TABLE constraints(
	id          SERIAL PRIMARY KEY,
	product_id  INTEGER REFERENCES products (id),
	type        VARCHAR(10) CHECK (type IN ('TIME', 'USAGES')),
	max         INTEGER,
	valid_range tstzrange,
	CONSTRAINT valid_for_type CHECK (
		(type = 'USAGES' AND max IS NOT NULL AND valid_range IS NULL) OR
		(type = 'TIME' AND max IS NULL AND valid_range IS NOT NULL)
	)
);

```text

INSERT INTO constraints (product_id, type, valid_range) VALUES (1, ‘TIME’, ‘[2024-01-01, 2025-01-01)’); INSERT INTO constraints (product_id, type, max) VALUES (1, ‘USAGES’, 100); INSERT INTO constraints (product_id, type, valid_range) VALUES (1, ‘TIME’, ‘[2024-02-01, 2025-02-01)’); INSERT INTO constraints (product_id, type, max) VALUES (1, ‘USAGES’, 101); INSERT 0 1 INSERT 0 1 INSERT 0 1 ERROR: duplicate key value violates unique constraint “unique_usages_constraint”DETAIL: Key (product_id)=(1) already exists. “`