postgres constraint based on SELECT condition

Question

ᕼ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!

Are you able to limit upserts to only be accomplished by a procedure that you define? — Shawn Mehan
– Shawn Mehan, Commented Mar 20, 2018 at 22:26

redneb · Accepted Answer · 2018-03-20 22:49:38Z

4

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.

answered Mar 20, 2018 at 22:49

redneb

24.3k7 gold badges46 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gershom Maes Over a year ago

Holy moly postgres is insane and this is exactly the answer I was looking for!

Gershom Maes Over a year ago

I have a quick followup question: can I achieve this with ALTER TABLE syntax?

redneb Over a year ago

No. If it wasn't for the WHERE part it would have been possible, but being a partial index, it has to be created with CREATE INDEX.

balsick · Accepted Answer · 2018-03-20 23:04:00Z

2

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

edited Mar 20, 2018 at 23:04

answered Mar 20, 2018 at 22:29

balsick

1,2111 gold badge11 silver badges29 bronze badges

3 Comments

Gershom Maes Over a year ago

This sounds promising - but how do I see that multiple children of the same age are appreciated BEFORE both those children are appreciated? It sounds like I would have to have some notion of the future intent of an incoming SQL query... I'd really appreciate some more detailed example code!

balsick Over a year ago

in the <code> section you have access to the mutating table and to the row that is going to be inserted or updated :)

Gershom Maes Over a year ago

Ah that sounds like it could work! I'd really appreciate a more detailed answer, this only really shows me how to define a trigger.

Parnell Kelley · Accepted Answer · 2018-03-20 22:32:02Z

0

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. :)

answered Mar 20, 2018 at 22:32

Parnell Kelley

11 bronze badge

1 Comment

Gershom Maes Over a year ago

Unfortunately parents can appreciate many children! Just not two children of the same age.

Collectives™ on Stack Overflow

postgres constraint based on SELECT condition

3 Answers 3

3 Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related