Oracle SQL Test to identify missing rows for unique identifier

Question

I am trying to find an Oracle SQL to test whether a table (CONTACTS) contains two rows of data for each primary key (PK) called REF_NO.

The table has a PK called REF_NO and a second PK called CONT_IND. CONT_IND can be either 1 or 2 - no other values possible, including NULL.

For each REF_NO, the table should contain:

one row where CONT_IND = 1
one row where CONT_IND = 2

I'm trying to find an output that shows:

FAIL: REF_NO 'X' CONT_IND 1 IS MISSING

or

FAIL: REF_NO 'X' CONT_IND 2 IS MISSING

If both rows are present, the output should show:

PASS

Please show sample input data, expected result, your best attempt and explain what exactly is unclear to you. — Jonas Metzler
– Jonas Metzler, Commented Nov 19 at 12:32
Not sure what you mean by and a second PK - relational database tables can ONLY ever have ONE primary key (or none - but never multiple!) - by design. — marc_s
– marc_s, Commented Nov 19 at 13:03
A database can have multiple unique keys and they function the same way as a primary key for all practical purposes. But OP is incorrect to refer to CONT_IND as a key of any kind if it's only a binary indicator (1 or 0). If it were a key, then the table could have only two rows in it. Perhaps what OP meant was it's a second column in the primary key definition, so the PK might actually be (REF_NO,CONT_ID). — Paul W
– Paul W, Commented Nov 19 at 14:12
Others are already answering aptly enough. I only want to add that you don't want to be in a situation where you try to enforce "there must be two rows", because that requires code rather than constraints. If there should be only two versions of an entity, then your modelling should have a head table defined as (REF_NO,PRIMARY_CONTACT_ID, SECONDARY_CONTACT_ID) where both columns are NOT NULL and a check constraint ensures they aren't the same, and then have a CONT table with surrogate key CONTACT_ID that defines them. That's a much more enforceable model than counting rows. — Paul W
– Paul W, Commented Nov 19 at 14:16

MatBailie · Accepted Answer · 2025-11-19 13:21:27Z

3

I'm assuming you mean "foreign keys" or perhaps "columns forming part of a composite key".

As stated elsewhere, a table can't have two PKs (or multiple rows with the same PK value).

As such, if just use a GROUP BY and conditional aggregation (aggregate with a CASE expression within it) to create separate columns for whether cont_ind 1 and/or 2 exist.

SELECT
  ref_no,
  MAX(CASE WHEN cont_ind = 1      THEN 1 ELSE 0 END)   AS has_cont_ind_1,
  MAX(CASE WHEN cont_ind = 2      THEN 1 ELSE 0 END)   AS has_cont_ind_2, 
  MAX(CASE WHEN cont_ind IN (1,2) THEN 0 ELSE 1 END)   AS has_illegal_cont_id
FROM
  contacts
GROUP BY
  ref_no

Then you could use a HAVING clause to pick out only problematic rows, or another layer of aggregation to summarise how many problematic rows you have.

answered Nov 19 at 13:21

MatBailie

87.4k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Guillaume Outters · Accepted Answer · 2025-11-19 13:36:44Z

When checking presence of no more than two values, I usually use the trick of comparing MIN() and MAX() which, whatever your RDBMS, are quasi certain to have been optimized for ages by the vendor.

In your case (with no NULL, all values being either 1 or 2, and no need to look for REF_NO not present in the table):

no need of a WHERE CONT_IND IN (1, 2) to focus on those two specific values (in fact you probably have a constraint for that),
so all you have to test for is MIN(CONT_IND) < MAX(CONT_IND) (if they differ, it means you have at least one 1, one 2)
to detect the missing one, simply test for 3 - [the present one]:
2 present → 3 - 2 = 1 → 1 is missing,
1 present → 3 - 1 = 2 → 2 is missing

As you didn't say what to do if we have more than 1 row for a REF_NO - CONT_IND pair, we don't handle it here, but you probably have a unique index on the pair which prevents that kind of behaviour.

So:

SELECT
  REF_NO,
  CASE
    WHEN MIN(CONT_IND) < MAX(CONT_IND) THEN 'PASS'
    ELSE 'FAIL: CONT_IND '||(3 - MAX(CONT_IND))||' IS MISSING'
  END TEST
FROM CONTACTS
GROUP BY REF_NO;

REF_NO	TEST
1	FAIL: CONT_IND 2 IS MISSING
2	FAIL: CONT_IND 1 IS MISSING
3	PASS

(see it running in a db<>fiddle)

Collectives™ on Stack Overflow

Oracle SQL Test to identify missing rows for unique identifier

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related