CODEWITHSUNDEEP
postgresqlconstraints
Gershom Maes
Gershom Maes

Reputation: 8140

postgres constraint based on SELECT condition

ᕼello! I think I have a somewhat tricky postgres situation:

parents have children. children have an age, and a flag that they are the appreciated.

The rule: a parent can't appreciate two children of the same age!

My question is: how to enforce this rule?

Current schema:

CREATE TABLE parent (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

CREATE TABLE child (
  id SERIAL PRIMARY KEY,
  parent INTEGER REFERENCES parent(id) NOT NULL,
  name VARCHAR(50) NOT NULL,
  age INTEGER NOT NULL,
  appreciated BOOLEAN NOT NULL
);

Put some values in:

INSERT INTO parent(name) VALUES
  ('bob'),    -- assume bob's id = 0
  ('mary');   -- assume mary's id = 1

INSERT INTO child(parent, name, age, appreciated) VALUES
  (0, 'child1', 10, FALSE), -- Bob has children 1, 2, 3
  (0, 'child2', 10, FALSE),
  (0, 'child3', 15, FALSE),
  (1, 'child4', 20, FALSE), -- Mary has children 4, 5, 6
  (1, 'child5', 20, FALSE),
  (1, 'child6', 10, FALSE);

All fine so far. No child is appreciated, which is always valid.

Mary is allowed to appreciate child6:

UPDATE child SET appreciated=TRUE WHERE name='child6';

Bob is allowed to appreciate child2. child2 is the same age as child6 (who is already appreciated), but child6 is not Bob's child.

UPDATE child SET appreciated=TRUE WHERE name='child2';

Bob now cannot appreciate child1. This child1 is the same age as child2, and child2 is already appreciated.

UPDATE child SET appreciated=TRUE WHERE name='child2'; -- This needs to FAIL!

How do I enforce such a constraint? I'm open to all kinds of solutions, but modifying the general schema is not an option.

Thanks in advance!

Upvotes: 0

Views: 1645

Answers (3)

balsick
balsick

Reputation: 1201

You might want to use a trigger that activates BEFORE the insert/update and that fails if the constraint you create is not satisfied. I suppose it should be like

create trigger <trigger_name>
before insert or update on <table_name>
for each row
declare
dummy number;
begin
select count(*)
into dummy
from <table_name>
where (appreciated=TRUE and :new.child = child and :new.age = age);

if dummy > 0 then
  raise_application_error(-20001,'Too many appreciated children');
end if;
end;

Some documentation

Upvotes: 2

redneb
redneb

Reputation: 23850

How about a UNIQUE partial index, like so:

CREATE UNIQUE INDEX ON child(parent,age) WHERE appreciated;

So every pair of parent,age has to be unique, but only when appreciated children are considered.

Upvotes: 4

Parnell Kelley
Parnell Kelley

Reputation: 1

The simplest thing I would think to do is add a flag grateful(?) == false to the parent model and when child.appreciated == true { parent.grateful == true }

Check the value of parent.grateful in the function that acts on child.appreciated. If parent.grateful == true return "sorry this parent has already shown their appreciation." LOL this is an interesting concept though. Good Luck. :)

Upvotes: 0

Related Questions