Ensuring that 1 row has 5 specific records referencing it Context: a TEST has many CASEs. Typical.

However, the catch is that a TEST instance must have exactly 5 instances of CASE (1 to 5).

I could do something like this:

create table TEST (
   ID           Integer                                   not null,
   CASE1_OK       varchar                                 not null,
   CASE2_OK       varchar                                 not null,
   CASE3_OK       varchar                                 not null,
   CASE4_OK       varchar                                 not null,
   CASE5_OK       varchar                                 not null,
   CHECK        (CASE1_OK IN ('PASS', 'FAIL', 'INVESTIGATE'))         ,
   ...
   constraint pk_TEST primary key (ID)
)   ;


That's obviously a violation of 1NF.

Is there a way of having a separate CASE table while ensuring that TEST record can not be inserted without having exactly 5 existing CASE rows for it?
This can be done with DDL alone but it's not at all trivial. The following will ensure that any test will either have 0 or 5 test cases:

create table test (
   test_id  integer  not null,
   constraint pk_test 
      primary key (test_id)
)   ;


create table test_case (
   test_id       integer   not null,
   case_no       smallint  not null,
   case_no_prev  smallint  not null,
   case_ok       text   not null,
   constraint  pk_test_case
      primary key  (test_id, case_no),
   constraint  fk_test__test_case
      foreign key  (test_id) 
         references  test (test_id),
   constraint  ck_case_ok
      check  ( case_ok in ('PASS', 'FAIL', 'INVESTIGATE') ),

   constraint  ck_case_numbers
      check  ( case_no between 1 and 5 ), 
   constraint  fk_test_case__test_case
      foreign key  (test_id, case_no_prev) 
         references  test_case (test_id, case_no),
   constraint  ck_cases_are_5
      check  ( case_no &gt; 1 and case_no_prev = case_no - 1
            or case_no = 1 and case_no_prev = 5 )
)   ;