concept code uniqueness constraint rule implementation

[sshenzha]

In OMOP CDM, when querying concepts by concept_code, this is unique only within a vocabulary, not globally. The same concept_code can exist in multiple vocabularies and refer to completely different things. For example:

-- BAD: returns rows from NDC *and* SNOMED *and* RxNorm
SELECT concept_id, concept_name, vocabulary_id
FROM base.concept
WHERE concept_code = '7980';
-- Returns 3 rows, 3 different concept_ids

-- GOOD: returns exactly one intended concept
SELECT concept_id, concept_name, vocabulary_id
FROM base.concept
WHERE concept_code = '7980'
  AND vocabulary_id = 'RxNorm';
-- Returns 1 row

[sshenzha]

This rule ensures that whenever a query filters the concept table by concept_code, it also filters by vocabulary_id in the same scope. concept_code alone is not unique across OMOP; the same code can exist in multiple vocabularies with completely different meanings. Without a vocabulary_id constraint, the query will silently match unintended concepts from other vocabularies, producing incorrect results.

The rule categorises every possible scenario into groups and applies different checks:

1. concept_code filter WITHOUT vocabulary_id in the same scope -> the query is ambiguous, concept_code alone does not uniquely identify a concept, and the query will silently match all concepts with that code across all vocabularies in the database.

-- ERROR: no vocabulary_id
SELECT concept_id FROM concept c
WHERE c.concept_code = '308136'

2. Mismatched table aliases -> the vocabulary_id filter is constraining a different instance of the concept table. The ambiguity for the original concept_code filter remains unresolved.

-- ERROR: concept_code on alias 'c', vocabulary_id on alias 'd'
SELECT c.concept_id
FROM concept c
JOIN concept d ON c.concept_id = d.concept_id
WHERE c.concept_code = '308136'          -- ← filtering alias c
  AND d.vocabulary_id = 'RxNorm'         -- ← filtering alias d (different table)
-- This produces a violation because c.concept_code is still ambiguous

3. Multiple concept_code filters in the same scope (one violation) -> If a SELECT has multiple concept_code filters on the same table alias (e.g., an OR condition), they all share the same missing vocabulary_id problem. Reporting it once is sufficient.

-- ERROR: produces ONE violation, not two
SELECT concept_id FROM concept c
WHERE c.concept_code = '123'
   OR c.concept_code = '456'

-- Both filters are in the same scope on the same alias, 
-- so only one violation is produced

4. Multiple table aliases, each missing vocabulary_id -> If the query references the concept table multiple times with different aliases (e.g., a self-join), each alias is tracked independently. Missing vocabulary_id on one alias does not forgive it on another.

-- ERROR: produces TWO violations
SELECT c1.concept_id, c2.concept_id
FROM concept c1
JOIN concept c2 ON c1.concept_id = c2.concept_id
WHERE c1.concept_code = '123'          -- ← violation 1: c1 missing vocabulary_id
  AND c2.concept_code = '456'          -- ← violation 2: c2 missing vocabulary_id