A 1:n relationship can always be reversed to be seen as n:1 . In other words, instead of:
parent:field1 -> child1:id
parent:field2 -> child2:id
parent:field3 -> child3:id
....
parent:field9 -> child9
you can always write:
child1:parent_id -> parent:id
child2:parent_id -> parent:id
child3:parent_id -> parent:id
....
child9:parent_id -> parent:id
... and constrain the number of children per parent via a trigger or in the application. That's the approach I would strongly recommend. You'll need a deferrable constraint trigger to allow you to insert anything.
If you want to enforce it in the database, use a constraint trigger. Given the dummy schema:
CREATE TABLE parent (id serial primary key);
CREATE TABLE child( id serial primary key, parent_id integer references parent(id) );
INSERT INTO parent (id) values ( DEFAULT );
INSERT INTO child ( parent_id )
SELECT p.id FROM parent p CROSS JOIN generate_series(1,9) x;
You could write:
CREATE OR REPLACE FUNCTION children_per_parent() RETURNS TRIGGER AS $$
DECLARE
n integer;
BEGIN
IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
SELECT INTO n count(id) FROM child WHERE parent_id = NEW.parent_id;
IF n <> 9 THEN
RAISE EXCEPTION 'During % of child: Parent id=% must have exactly 9 children, not %',tg_op,NEW.parent_id,n;
END IF;
END IF;
IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
SELECT INTO n count(id) FROM child WHERE parent_id = OLD.parent_id;
IF n <> 9 THEN
RAISE EXCEPTION 'During % of child: Parent id=% must have exactly 9 children, not %',tg_op,NEW.parent_id,n;
END IF;
END IF;
RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';
CREATE CONSTRAINT TRIGGER children_per_parent_tg
AFTER INSERT OR UPDATE OR DELETE ON child
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE children_per_parent();
CREATE OR REPLACE parent_constrain_children() RETURNS trigger AS $$
DECLARE
n integer;
BEGIN
IF TG_OP = 'INSERT' THEN
SELECT INTO n count(id) FROM child WHERE parent_id = NEW.id;
IF n <> 9 THEN
RAISE EXCEPTION 'During INSERT of parent id=%: Must have 9 children, found %',NEW.id,n;
END IF;
END IF;
-- No need for an UPDATE or DELETE check, as regular referential integrity constraints
-- and the trigger on `child' will do the job.
RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';
CREATE CONSTRAINT TRIGGER parent_limit_children_tg
AFTER INSERT ON parent
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE parent_constrain_children();
Note that there are two triggers above. The trigger on child is obvious. The trigger on parent is needed to prevent insertion of a parent without any children.
Now observe a test:
regress=# delete from child;
ERROR: During DELETE: Parent id 1 must have exactly 9 children, not 0
regress=# insert into child( parent_id) SELECT id FROM parent;
ERROR: During INSERT: Parent id 1 must have exactly 9 children, not 10
Because the deferred constraint trigger is checked when the transaction commits, not immediately or at the end of the statement, you can still do this:
regress# BEGIN;
BEGIN
regress# INSERT INTO parent (id) values ( DEFAULT ) RETURNING id;
id
----
2
INSERT 0 1
regress# insert into child ( parent_id ) SELECT p.id FROM parent p CROSS JOIN generate_series(1,9) x WHERE p.id = 4;
INSERT 0 9
regress# COMMIT;
COMMIT
... but if you change the "generate_series" max to 8 or 10, or leave off inserting any children entirely, COMMIT will fail like, eg:
regress=# commit;
ERROR: During INSERT: Parent id 5 must have exactly 9 children, not 8
If you only require each parent to have a maximum of 9 children, not exactly 9 children as implemented in the above trigger, you can remove the DEFERRABLE INITIALLY DEFERRED, change the <> 9 to <= 9, and chop out the DELETE handler in the child trigger.
BTW, if I were working with JPA in Java or some other reasonably clever ORM I'd just constrain the size of the collection of children on the parent:
@Entity
public Parent {
@Column
@Size(min=9,max=9)
private Collection<Child> collectionOfChildren;
}
Way simpler, albeit not enforced at the database level.
