Justin Cave's answer here and Tom Kyte's pointed me to a solution using a function based index. I think this can be made even simpler with some more thought but this works now:
CREATE OR REPLACE FUNCTION UNIQUE_START_STAGE (
phase_id_in IN NUMBER,
stage_id_in IN NUMBER)
RETURN NUMBER
DETERMINISTIC
IS
-- PURPOSE:enforce business logic that a phase can have only one stage where
-- the disabled field has a value of 0 and IS_START_STAGE has a value of 1
v_count NUMBER (9);
BEGIN
SELECT COUNT (s.id)
INTO v_count
FROM STAGE s
WHERE S.IS_START_STAGE = 1
AND s.disabled = 0
AND S.PHASE_ID = phase_id_in;
IF v_count = 1
THEN
--return the primary key if there is only one
v_count := stage_id_in;
ELSIF v_count < 1
THEN
v_count := NULL;
END IF;
RETURN v_count;
END UNIQUE_START_STAGE;
and then we create an index based the idea that there can only be one child stage that is enabled for a phase that is the start stage
CREATE UNIQUE INDEX unique_start_stage_idx
ON stage (
CASE
WHEN disabled = 1 THEN NULL
WHEN is_start_stage = 0 THEN NULL
ELSE UNIQUE_START_STAGE (phase_id, id)
END);
--and add the same constraint to the other table
CREATE UNIQUE INDEX unique_start_stage_idx2
ON PHASE_FIRST_STAGE (
UNIQUE_START_STAGE (phase_id, stage_id));
This solution partially solves the problem:
- it enforces that there is only one entry in STAGE for each value of PHASE_ID where IS_START_STAGE =1 and DISABLED = 0
- it enforces this same uniqueness in PHASE_FIRST_STAGE
- it does not enforce that an entry in STAGE is also in PHASE_FIRST_STAGE
- you could replace the PHASE_FIRST_STAGE table with a view of STAGE that cleans up the last issue
A partial unique index should do this:
create unique index max_one_null
on item (type_id)
where manufactured_until is null;
For bonus points, is there a reasonably complex way to guard that the intervals do not overlap for one item type
Look into range types and exclusion constraints. They were specifically designed for this problem.
Something like this (untested):
create table item
(
id int,
type_id int,
manufactured_during tsrange
);
The inserts will look a bit different as there is only a single column now:
insert into item
(id, type_id, manufactured_during)
values
(1, 101, '[2000-01-01,2012-12-31]'),
(1, 101, '[2013-01-01,)');
[2000-01-01,2012-12-31]
defines a closed interval that includes both dates. [2013-01-01,)
defines an open interval without an end (maps to manufactured_until is null
with your current table design)
Then add an exclusion constraint to guard the range:
alter table item
add constraint check_manufactured_range
exclude using gist (type_id with =, manufactured_during with &&);
You don't need a unique index any more because the constraint will make sure that nothing will overlap.
More information in the manual: http://www.postgresql.org/docs/current/static/rangetypes.html
If you search the internet for "Postgres exclusion constraints" you will find several presentations and blog posts regarding this topic.
P.S.: it seems that you actually want a daterange
(instead of a "timestamp range") because you probably don't want to include the time of day with the intervals that define when something was manufactured.
Best Answer
You don't need triggers or PL/pgSQL at all.
You don't even need
DEFERRABLE
constraints.And you don't need to store any information redundantly.
Include the ID of the active email in the
users
table, resulting in mutual references. One might think we need aDEFERRABLE
constraint to solve the chicken-and-egg problem of inserting a user and his active email, but using data-modifying CTEs we don't even need that.This enforces exactly one active email per user at all times:
Remove the
NOT NULL
constraint fromusers.email_id
to make it "at most one active email". (You can still store multiple emails per user, but none of them is "active".)You can make
active_email_fkey
DEFERRABLE
to allow more leeway (insert user and email in separate commands of the same transaction), but that's not necessary.I put
user_id
first in theUNIQUE
constraintemail_fk_uni
to optimize index coverage. Details:Optional view:
Here's how you insert new users with an active email (as required):
The specific difficulty is that we have neither
user_id
noremail_id
to begin with. Both are serial numbers provided from the respectiveSEQUENCE
. It can't be solved with a singleRETURNING
clause (another chicken-and-egg problem). The solution isnextval()
as explained in detail in the linked answer below.If you don't know the name of the attached sequence for the
serial
columnemail.email_id
you can replace:with
Here's how you add a new "active" email:
SQL Fiddle.
You might encapsulate the SQL commands in server-side functions if some simple-minded ORM isn't smart enough to cope with this.
Closely related, with ample explanation:
Also related:
About
DEFERRABLE
constraints:About
nextval()
andpg_get_serial_sequence()
: