0

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?

2

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 > 1 and case_no_prev = case_no - 1
            or case_no = 1 and case_no_prev = 5 )
)   ;
  • Out of curiosity, does Postgres by default defer constraint checking until the commit of a transaction? You might need to set the constraint fk_test_case__test_case as deferred. – mruether Oct 15 '14 at 7:10
  • 1
    @mruether, no, the default is not to defer. But if you insert all the (5) cases of a test with one statement, there is no need to. – ypercubeᵀᴹ Oct 15 '14 at 8:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.