Composite Keys: Match Complete Pairs Instead of Separate Lists

Composite keys need a complete pair match when the allowed values are defined together. I don’t split the components into independent lists. Doing so can admit combinations that were never allowed.

A tenant identifier and a code can each look valid on their own. Their combination may still refer to a different record. The query must preserve the relationship between the components, not only their individual membership.

Make the incorrect combinations visible

The allowed input contains two pairs: tenant ten with code A, and tenant twenty with code B. The candidate input contains those pairs and their crossed combinations. A fifth candidate matches neither component.

IndependentListsMatch tests tenant and code against separate IN subqueries. CompletePairMatch uses one EXISTS subquery with both comparisons against the same allowed row. The two diagnostic columns expose their different meanings.

The example uses nonmissing key components and five complete candidate rows. It is a pure SELECT query with no access-control changes. Its match flags demonstrate selection semantics rather than grant any actual permission.

WITH Candidates AS
(
    SELECT RowId, TenantId, Code
    FROM (VALUES (1, 10, CAST('A' AS varchar(1))),
                 (2, 10, 'B'), (3, 20, 'A'), (4, 20, 'B'), (5, 30, 'C')) AS v(RowId, TenantId, Code)
), Allowed AS
(
    SELECT TenantId, Code FROM (VALUES (10, CAST('A' AS varchar(1))), (20, 'B')) AS v(TenantId, Code)
)
SELECT c.RowId, c.TenantId, c.Code,
       CASE WHEN c.TenantId IN (SELECT TenantId FROM Allowed)
             AND c.Code IN (SELECT Code FROM Allowed) THEN 1 ELSE 0 END AS IndependentListsMatch,
       CASE WHEN EXISTS (SELECT 1 FROM Allowed AS a
                         WHERE a.TenantId = c.TenantId AND a.Code = c.Code)
            THEN 1 ELSE 0 END AS CompletePairMatch
FROM Candidates AS c
ORDER BY c.RowId;

Read the false matches

Candidate one matches the allowed pair ten and A. Candidate four matches twenty and B. Both diagnostic expressions should return one for those rows.

Candidate two contains ten and B. Ten appears in the allowed tenant list, and B appears in the allowed code list. The independent expression therefore returns one despite there being no allowed row with that pair.

Candidate three has the opposite crossed combination, twenty and A. It has the same problem. The complete-pair expression returns zero for both crossed candidates because neither satisfies both comparisons on one allowed row.

Candidate five contains thirty and C, so both expressions return zero. This ordinary nonmatch is useful, but it does not expose the relationship error. The crossed combinations are the important tests.

Keep the match row-preserving

EXISTS answers whether at least one qualifying allowed row is present. Duplicate allowed rows would not multiply the candidate output here. That is useful when the desired result is one match decision per candidate.

A join on both key components can also express pair matching. However, multiple matching allowed rows can multiply joined output. Decide whether existence or joined row detail is the intended result before choosing the form.

Adding a third component requires adding its comparison to the same correlated predicate. Splitting any component back into an independent list can reintroduce invalid combinations. Keep the entire key definition visible during review.

Avoid concatenating components into an ambiguous display string as a shortcut. Delimiters, conversions and collation choices can introduce separate matching problems. Compare the typed components directly when that is the defined key.

Define missing-key behavior separately

All components in this bounded example are populated. An ordinary equality predicate does not treat two NULL values as an equal key. A design that permits missing components needs an explicit matching rule and additional tests.

A display label and an identifier are also different contracts. Case or accent matching may matter for text components. Choose the intended collation and data type rather than assuming every visible code is compared identically.

This query does not benchmark IN, EXISTS or joins. It shows that the independent-list predicate asks a different logical question. An efficient implementation of the wrong predicate still returns the wrong candidate population.

When adapting the check, include at least one crossed combination. Keep a true match and an ordinary nonmatch too. That small test set verifies that the relationship survives rather than checking only that individual values look familiar.

A small crossed test now saves a surprise later.

A permission is not two separate lists, it is one allowed pair matched whole.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.