constraint in oracle

I'm trying modeling things like this using oracle:

An account can have 1~5 members

So I'll have a account table and a member table with a FK of account. But is that possible to check if the account have less than 5 members?

I've tried constraint and trigger but oracle doesn't allow sub queries in constraint or condition of trigger like

CHECK( SELECT ...) or IF count(*) >= 5

which make things getting hard.

One way is to add a memberNo column and restrict to 5 possible values, per account:

CREATE TABLE members
( -- ...
  accountID NUMBER REFERENCES accounts (accountID),
  memberNo NUMBER NOT NULL,
  -- ...,
  UNIQUE (accountID, memberNo),
  CHECK (memberNo IN (1,2,3,4,5))
) ;

This imposes of course the additional burden of providing and keeping values for this column when inserting into the table (with extra complications for deletions. If for example you delete the member with memberNo=2 for an account, should the 3 higher numbers be decreased or should the complications be dealt in future inserts.)

StackExchange Database Administrators Q#63996, answer score: 4