SQL/Oracle: Is it possible to have a group function in a check constraint?

Question

My Tables are:

 CREATE TABLE member
             (
                          svn      INTEGER,
                          campid   INTEGER,
                          tentname VARCHAR(4),
                          CONSTRAINT member_fk_svn FOREIGN KEY (svn) REFERENCES people,
                          CONSTRAINT member_fk_campid FOREIGN KEY (campid) REFERENCES camp ON
             DELETE CASCADE,
                    CONSTRAINT member_pk PRIMARY KEY (svn, campid),
                    CONSTRAINT member_fk_tentname FOREIGN KEY (tentname) REFERENCES tent,
                    CONSTRAINT check_teilnehmer_zelt CHECK (Count(zeltname) over (PARTITION BY (zeltname
             AND    lagerid)) )<= zelt.schlafplaetze
             ); 

With the last constraint, I want to check that there are not more members assigned to a tent than the capacity of it.

Answer 1

This would require a SQL assertion, which is not currently supported by Oracle (or indeed any DBMS). However, Oracle are considering adding support for these in the future (please upvote that idea!)

Solution using a Materialized View

Currently you may be able to implement this constraint using a materialized view (MV) with a check constraint - something I blogged about many years ago. In your case the materialized view query would be something like:

select t.tent_id
  from tents t, members m
 where m.tent_id = t.tent_id
 group by t.tent_id
 having sum(m.num_members) > t.capacity;

The check constraint could be:

check (t.tent_id is null)

The check constraint would be violated for any row returned by the materialized view, so ensures that the MV is always empty i.e. no tents exist that are over capacity.

Notes:

1. I deliberately did not use ANSI join syntax, since MVs don't tend to like it (the same join may be permitted in old syntax but not permitted in ANSI syntax). Of course feel free to try ANSI first.
2. I haven't confirmed that this particular query is permitted in an MV with REFRESH COMPLETE ON COMMIT. The rules on what can and cannot be used vary from version to version of Oracle.
3. Watch out for the performance impact of maintaining the MV.

Alternative solution using a trigger

Another way would be to add a column total_members to the tents table, and use a trigger on members to maintain that e.g.

create trigger members_trg
  after insert or delete or update of num_members on members
  for each row
  declare
    l_total_members tents.total_members%type;
  begin
    select total_members
      into l_total_members
      from tents
     where tent_id = nvl(:new.tent_id,:old.tent_id)
       for update of total_members;

    if inserting then
      l_total_members := l_total_members + :new.num_members;
    elsif deleting then
      l_total_members := l_total_members - :old.num_members;
    elsif updating then
      l_total_members := l_total_members - :old.num_members + :new.num_members;
    end if;

    update tents
       set total_members = l_total_members 
     where tent_id = nvl(:new.tent_id,:old.tent_id);
  end;

Then just add the check constraint:

alter table tents add constraint tents_chk
  check (total_members <= capacity);

By maintaining the total in the tents table, this solution serializes transactions and thus avoids the data corruption you will get with other trigger-based solutions in multi-user environments.