In parent-child relationships, it’s common you’ll want to ensure there’s at least one child row for every parent row. For example, every account must have a payment method, every airport must have a runway, and every team must have a player.

This is a one-to-at-least-one relationship. For every parent row there must be one or more matching child rows. Oracle AI Database offers two constraints you can use to enforce this:

Read the discussion to help you choose which of these to use or check the cheat sheet.

An assertion ensuring every parent row has at least one matching child row. create assertion at_least_one_child_per_parent check ( all ( select id from parent ) par satisfy ( exists ( select 'a child' from child chd where chd.parent_id = par.id ) ) ) deferrable initially deferred

Guarantee one-to-at-least-one with assertions

Assertions enable you to define cross-row, cross-table constraints.

They have two key parts:

  • all: a subquery that returns the rows to check.
  • satisfy: a Boolean expression; the database rejects rows where this is false.

So, to ensure there’s at least one player on every team:

  • select all the teams.
  • satisfy there exists a player for this team.

Giving an assertion like:

/* Ensure there is a player listed for every team */
create assertion at_least_one_player_per_team
check (
  all ( select team_id from teams ) team
  satisfy (
    exists (
      select 'a player'
      from   players play
      where  play.team_id = team.team_id
    )
  )
)
deferrable initially deferred;

This is a simple, declarative way to define the rule. But assertions were only added in release 23.26.1. If you’re working with older releases, you’ll need to use the foreign key method below.

Before we get to that, note the deferrable initially deferred clause in the assertion. By default, the database validates constraints after each statement. Meaning it checks the assertion is true as soon as you insert the parent row. But child rows don’t yet exist, so the assertion raises an error!

/* Force assertion to be checked at the statement level */
set constraint all immediate;

/* There are no players assigned to the team, so the assertion raises an error */
insert into teams 
set    team_id = 1, 
       team_name = 'No players';
ORA-08601: SQL assertion (CHRIS.AT_LEAST_ONE_PLAYER_PER_TEAM) violated.

You also can’t insert any players because they have a foreign key back to the team. Deferring the constraint delays validation until commit. This avoids the circular dependency problem.

For example:

/* Defer the assertion until commit */
set constraint at_least_one_player_per_team deferred; 

/* The insert now succeeds...*/
insert into teams 
set    team_id = 1, 
       team_name = 'No players';

/* ...but the commit doesn't if there are no players */
commit;
ORA-02091: transaction rolled back
ORA-08601: SQL assertion (CHRIS.AT_LEAST_ONE_PLAYER_PER_TEAM) violated.

/* Insert the team and player in the same transaction */
insert into teams 
set    team_id = 1, 
       team_name = 'One player';

insert into players
set    player_id = 1, 
       player_name = 'First player',
       team_id = 1;

/* Commit now succeeds because the team has a player */
commit;

The simplicity of assertions makes these an obvious choice if you’re running the latest release of Oracle AI Database. If you’re working with older releases, you need use the foreign key method. To do this you need to extend the schema. As we’ll see this, is something you may want to do whichever version you’re using.

Using foreign keys to enforce one-to-at-least-one relationships in the database. Have a regular foreign key from the child to the parent. Then also have a column on the parent table which nominates a primary row from its children. Place an FK from parent to child on this column.

Ensure one-to-at-least-one with foreign keys

The traditional way to enforce one-to-at-least-one relationships is with a mandatory column on the parent. This parent column has a foreign key to the child table. Thus, a child row must exist for every parent row, enforcing the at-least-one part of the rule.

To do this you need a way to pick which child row to reference from the parent. Often there’s a clear candidate for this. This happens when one of the child rows is the primary, owning, or default row for its parent. For example:

  • Every account must have a default payment method.
  • Every airport must have a primary runway.
  • Every team must have a captain.

So, you can ensure every team has at least one player by stating its captain on the teams table. This captures a real rule, so consider this modelling technique over others, such as a flag on the players table to say if someone is the captain.

The code below adds this column to the teams table with a foreign key to players:

/* Drop assertion to use FK method instead */
drop assertion at_least_one_player_per_team;

/* Add a captain column to the teams table
   Have a DEFERRABLE foreign key from the captain to players; 
   This ensures there is at least one player for every team */
alter table teams
  add ( 
    captain_player_id, 
    constraint team_captain_fk
      foreign key ( captain_player_id )
      references players
      deferrable initially deferred
  );

/* Set a captain for every team */
update teams team
set    captain_player_id = (
  select max ( player_id )
  from   players play
  where  play.team_id = team.team_id
);

/* Make the new column mandatory to ensure every team has a captain */
alter table teams 
  modify captain_player_id not null;

This creates a circular dependency between the parent and child. So, as with assertions, either the foreign key or not null constraint must be deferrable.

With this in place, every team must have a captain:

insert into teams 
set    team_id = 2, 
       team_name = 'Team with captain',
       captain_player_id = 2;

insert into players
set    player_id = 2, 
       player_name = 'Captain',
       team_id = 2;

commit;

But this implementation is incomplete.

While the foreign key ensures the captain is a player, it could be a player assigned to a different team!

For example, you can make one player the captain for many teams:

/* Every team must have a captain... */
insert into teams 
set    team_id = 3, 
       team_name = 'Captain from wrong team',
       captain_player_id = 1;
/* ...but it could be any player on any team! */

commit;

To avoid this, the foreign key from the parent back to the child must include the parent’s primary key. If, as in this example, the child table’s primary key doesn’t include the parent’s, you need to create a fake unique constraint on the child which does. Then have the parent foreign key point to this unique constraint.

For example:

/* Add unique constraint including the parent key
   The captain FK will reference this */
alter table players
  add constraint team_player_u 
  unique ( team_id, player_id );

/* Remove the old FK */
alter table teams
  drop constraint team_captain_fk;

/* Add an FK that references the unique constraint on teams */
alter table teams
  add constraint team_captain_fk
    foreign key ( team_id, captain_player_id )
    references players ( team_id, player_id )
    deferrable initially deferred
    novalidate;

/* Errors because captain is a player on a different team */
insert into teams 
set    team_id = 4, 
       team_name = 'Captain from wrong team',
       captain_player_id = 1;

commit;
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (CHRIS.TEAM_CAPTAIN_FK) violated - parent key not found

/* Insert team and captain playing for that team */
insert into teams 
set    team_id = 4, 
       team_name = 'Correct captain',
       captain_player_id = 4;
  
insert into players 
set    player_id = 4, 
       player_name = 'Captain team 4',
       team_id = 4;

commit;

This method also adds an extra step if you want to remove the captain from a team. You need to assign another player to remove them:

/* To remove the captain, you must have another player... */
insert into players 
set    player_id = 5, 
       player_name = 'New captain team 4',
       team_id = 4;

/* ...that you can assign as captain of the team */
update teams 
set    captain_player_id = 5
where  team_id = 4;

/* allowing you to remove the old captain */
delete players 
where  player_id = 4;

Using foreign keys to implement one-to-at-least-one works best when there’s a clear rule to pick one of the children. But it still works when none of the child rows could be considered the primary row.

In these cases, you’ll have to come up with your own method to choose which child row the parent references. The downside of doing this is people may infer meaning you didn’t intend from this relationship. You’ll have to balance how important the at-least-one rule is against any confusion picking any child row may bring.

Choosing how to enforce one-to-at-least-one relationships

If you’re running a release of Oracle AI Database before 23.26.1, the decision is made: you can only use foreign keys. When there’s an obvious way to pick which child row each parent should reference this can be the best approach. It captures the rule in a way you can see by glancing at the schema.

That said, assertions give several advantages. They save an extra update when you want to delete the primary child row for a parent and give you the flexibility to conditionally apply the at-least-one rule.

Because the all clause defines which rows the rule applies to, you can check a subset of the data. For example, you may only need to ensure active teams have at least one player; inactive teams can have no assigned players.

To do this, filter out inactive teams in the all clause:

/* Ensure active teams have at least one player 
   Inactive teams can have no players */
create assertion at_least_one_player_per_active_team
check (
  all ( select team_id from teams where is_active ) team
  satisfy (
    exists (
      select 'a player'
      from   players play
      where  play.team_id = team.team_id
    )
  )
)
deferrable initially deferred;

Assertions also provide a clear way to enforce the at-least-one rule when there is no obvious way to pick which child row a parent should reference. They enable you to express the logic with simple SQL without stretching the data model beyond its capacity.

If assertions are unavailable to you and there’s no clear candidate child row for each parent, you face a tough choice. Either define a synthetic rule for choosing a child, possibly introducing unintended meaning, or leave the rule unenforced in the database.

Decision cheat sheet

One-to-at-least-one relationships are common. Here’s a cheat sheet summarising how you choose which constraints to use to guarantee the at-least-one part of this rule.

Enforcing every parent has at least one child decision matrix. Does model have the concept of a primary child for each parent? If so, use a parent → primary child FK 23.26.1 & later consider assertions If not, use assertions in 23.26.1 & later In earlier releases either choose a synthetic primary child or do nothing in the database.

Create these tables to run the examples in this post:

create table teams ( 
  team_id   integer primary key, 
  team_name varchar2(255) not null,
  is_active boolean default false
);

create table players ( 
  player_id   integer primary key, 
  player_name varchar2(255) not null,
  team_id     references teams not null
);