In a composite FOREIGN KEY, what does MATCH FULL change compared with the default MATCH SIMPLE?

seniornice to knowfreq 20%

answer

only matters when some key columns are NULL
one column missing, the rest ignored
all-or-nothing versus any-NULL-passes
the clause sits before ON DELETE

basics

~10 s

MATCH SIMPLE, the default, treats the constraint as satisfied as soon as any referencing column is NULL, skipping the parent lookup. MATCH FULL allows only all-NULL or all-non-NULL: a half-filled composite key is rejected.

solid answer

~50 s

The optional MATCH clause sits between the REFERENCES column list and the referential actions, and it only matters when a composite foreign key has NULLs in some of its columns.

Under MATCH SIMPLE, the default when you write nothing, the constraint is satisfied if any referencing column is NULL — the engine performs no parent lookup at all. So ('CS', NULL) is accepted even when no matching parent exists, which surprises people who expected the non-NULL part to be checked.

Under MATCH FULL, the referencing columns must be either all NULL — satisfied, no lookup — or all non-NULL, in which case a matching parent row is required. A partly populated key is rejected outright, which is usually the intended rule for an optional relationship on a multi-column key.

The standard also defines MATCH PARTIAL, where the non-NULL columns must match some parent row. Engine support for MATCH varies, so check yours before designing around it.

The optional `MATCH` clause sits between the `REFERENCES` column list and the referential actions, and it only matters when a **composite** foreign key has NULLs in some of its columns. Under `MATCH SIMPLE`, the default when you write nothing, the constraint is satisfied if *any* referencing column is NULL — the engine performs no parent lookup at all. So `('CS', NULL)` is accepted even when no matching parent exists, which surprises people who expected the non-NULL part to be checked. Under `MATCH FULL`, the referencing columns must be either **all** NULL — satisfied, no lookup — or **all** non-NULL, in which case a matching parent row is required. A partly populated key is rejected outright, which is usually the intended rule for an optional relationship on a multi-column key. The standard also defines `MATCH PARTIAL`, where the non-NULL columns must match some parent row. Engine support for `MATCH` varies, so check yours before designing around it.

code

sql · 13 lines

sql

CREATE TABLE course_section (
    section_id BIGINT  NOT NULL,
    dept_code  CHAR(4),
    course_no  INTEGER,
    CONSTRAINT pk_course_section PRIMARY KEY (section_id),
    CONSTRAINT fk_course_section_course
        FOREIGN KEY (dept_code, course_no)
        REFERENCES course (dept_code, course_no)
        MATCH FULL
);

-- ('CS', NULL) is rejected under MATCH FULL,
-- but accepted under the default MATCH SIMPLE with no parent lookup

go deeper

for a junior

You are unlikely to be asked this. If it comes up, know that MATCH is an optional clause on a foreign key and that it only concerns NULLs in multi-column keys.

for a middle

Be able to state the default: with MATCH SIMPLE, any NULL in a composite referencing key satisfies the constraint with no parent lookup at all.

for a senior

Recognise the half-populated-row failure mode in a real schema and give the fix ladder: NOT NULL columns first, MATCH FULL where the relationship is optional, an all-or-nothing CHECK where MATCH is unsupported.

for a principal

Own the portability call — decide whether the schema may depend on a clause with uneven engine support, and how such assumptions get verified rather than assumed at deployment time.

Where the clause goes

sql

CONSTRAINT fk_section_course
    FOREIGN KEY (dept_code, course_no)
    REFERENCES course (dept_code, course_no)
    MATCH FULL
    ON DELETE CASCADE

MATCH is optional, appears after the referenced column list and before the ON DELETE / ON UPDATE clauses, and takes one of SIMPLE, PARTIAL or FULL. It is meaningful only for composite foreign keys with nullable referencing columns — with a single column, or with all columns declared NOT NULL, the three modes cannot be told apart.

The three modes

Given a child row whose referencing columns are (dept_code, course_no):

MATCH SIMPLE (the default when the clause is omitted): if any referencing column is NULL, the constraint is satisfied and no parent is looked up. Only when every column is non-NULL must a matching parent row exist.
MATCH FULL: if every referencing column is NULL, the constraint is satisfied with no lookup. If every column is non-NULL, a matching parent row is required. Any mixture of NULL and non-NULL is rejected.
MATCH PARTIAL: the non-NULL columns must match the corresponding columns of at least one parent row; the NULL ones are treated as wildcards.

So the interesting row is the mixed one:

sql

INSERT INTO course_section (dept_code, course_no, section_no)
VALUES ('CS', NULL, 1);

Under the default this row is accepted no matter what course contains. Under MATCH FULL it is rejected because the key is half-filled.

Why the default surprises people

The intuition most people carry is "a foreign key means the value has to exist over there". With a single nullable column that intuition is close enough — NULL means "no relationship", and everything else is checked. With a composite key the default quietly extends "no relationship" to "any NULL anywhere", so a row can claim a department that does not exist as long as the course number is missing. Nothing rejects it, nothing logs it, and the row sits in the table looking like data.

The practical consequence is a class of half-populated rows that neither the constraint nor most application code notices, and that break joins later in ways that look like missing reference data rather than a modelling error.

Making the rule explicit

There are three ways to get the guarantee you probably wanted, in descending order of preference:

Declare the columns NOT NULL when the relationship is mandatory. This is the strongest and most portable answer, and it makes the MATCH question moot — all three modes behave identically once no column can be NULL.
Declare MATCH FULL when the relationship is genuinely optional but must be all-or-nothing. This states the intent in the schema, where it belongs.
Add a table-level CHECK expressing all-or-nothing, when your engine does not implement MATCH FULL:

sql

CONSTRAINT ck_section_course_all_or_none CHECK (
    (dept_code IS NULL     AND course_no IS NULL)
 OR (dept_code IS NOT NULL AND course_no IS NOT NULL)
)

Combined with the default foreign key, that check plus the default's behaviour gives the same outcome: mixed rows are rejected by the check, all-NULL rows pass both, all-non-NULL rows are validated by the foreign key.

Portability

MATCH is part of the SQL standard, but implementation is uneven. PostgreSQL implements MATCH SIMPLE and MATCH FULL and documents MATCH PARTIAL as not implemented. Other engines may accept the syntax without honouring it, or reject it outright. Two consequences follow. First, never design a schema around MATCH PARTIAL; it is the least implemented mode and the wildcard semantics are hard to reason about anyway. Second, before relying on MATCH FULL in a portable schema, verify on your target engine that a mixed row is actually rejected — testing the behaviour is a two-line insert, and an unhonoured clause is worse than no clause because it reads as a guarantee.

What this is not

MATCH says nothing about what happens to child rows when the parent is deleted or its key changes — that is the separate ON DELETE / ON UPDATE territory, and the two clauses are independent. MATCH only decides which child rows must be looked up at all, given NULLs in the referencing columns.

## Where the clause goes ```sql CONSTRAINT fk_section_course FOREIGN KEY (dept_code, course_no) REFERENCES course (dept_code, course_no) MATCH FULL ON DELETE CASCADE ``` `MATCH` is optional, appears after the referenced column list and before the `ON DELETE` / `ON UPDATE` clauses, and takes one of `SIMPLE`, `PARTIAL` or `FULL`. It is meaningful only for composite foreign keys with nullable referencing columns — with a single column, or with all columns declared `NOT NULL`, the three modes cannot be told apart. ## The three modes Given a child row whose referencing columns are `(dept_code, course_no)`: - **MATCH SIMPLE** (the default when the clause is omitted): if *any* referencing column is NULL, the constraint is satisfied and no parent is looked up. Only when every column is non-NULL must a matching parent row exist. - **MATCH FULL**: if *every* referencing column is NULL, the constraint is satisfied with no lookup. If *every* column is non-NULL, a matching parent row is required. Any mixture of NULL and non-NULL is rejected. - **MATCH PARTIAL**: the non-NULL columns must match the corresponding columns of at least one parent row; the NULL ones are treated as wildcards. So the interesting row is the mixed one: ```sql INSERT INTO course_section (dept_code, course_no, section_no) VALUES ('CS', NULL, 1); ``` Under the default this row is accepted no matter what `course` contains. Under `MATCH FULL` it is rejected because the key is half-filled. ## Why the default surprises people The intuition most people carry is "a foreign key means the value has to exist over there". With a single nullable column that intuition is close enough — NULL means "no relationship", and everything else is checked. With a composite key the default quietly extends "no relationship" to "any NULL anywhere", so a row can claim a department that does not exist as long as the course number is missing. Nothing rejects it, nothing logs it, and the row sits in the table looking like data. The practical consequence is a class of half-populated rows that neither the constraint nor most application code notices, and that break joins later in ways that look like missing reference data rather than a modelling error. ## Making the rule explicit There are three ways to get the guarantee you probably wanted, in descending order of preference: 1. **Declare the columns NOT NULL** when the relationship is mandatory. This is the strongest and most portable answer, and it makes the `MATCH` question moot — all three modes behave identically once no column can be NULL. 2. **Declare `MATCH FULL`** when the relationship is genuinely optional but must be all-or-nothing. This states the intent in the schema, where it belongs. 3. **Add a table-level CHECK** expressing all-or-nothing, when your engine does not implement `MATCH FULL`: ```sql CONSTRAINT ck_section_course_all_or_none CHECK ( (dept_code IS NULL AND course_no IS NULL) OR (dept_code IS NOT NULL AND course_no IS NOT NULL) ) ``` Combined with the default foreign key, that check plus the default's behaviour gives the same outcome: mixed rows are rejected by the check, all-NULL rows pass both, all-non-NULL rows are validated by the foreign key. ## Portability `MATCH` is part of the SQL standard, but implementation is uneven. PostgreSQL implements `MATCH SIMPLE` and `MATCH FULL` and documents `MATCH PARTIAL` as not implemented. Other engines may accept the syntax without honouring it, or reject it outright. Two consequences follow. First, never design a schema around `MATCH PARTIAL`; it is the least implemented mode and the wildcard semantics are hard to reason about anyway. Second, before relying on `MATCH FULL` in a portable schema, verify on your target engine that a mixed row is actually rejected — testing the behaviour is a two-line insert, and an unhonoured clause is worse than no clause because it reads as a guarantee. ## What this is not `MATCH` says nothing about what happens to child rows when the parent is deleted or its key changes — that is the separate `ON DELETE` / `ON UPDATE` territory, and the two clauses are independent. `MATCH` only decides which child rows must be looked up at all, given NULLs in the referencing columns.

If your engine does not implement MATCH FULL, how do you get the same guarantee?
Declare the referencing columns NOT NULL when the relationship is mandatory — then all MATCH modes coincide. When it is genuinely optional, add a table-level CHECK requiring the columns to be all NULL or all non-NULL, and let the default foreign key validate the fully populated rows.
Where exactly does MATCH sit in a FOREIGN KEY declaration?
After the REFERENCES clause and its column list, and before ON DELETE and ON UPDATE. It is optional; omitting it means MATCH SIMPLE. It is independent of the referential actions — MATCH governs which child rows are checked at all, the actions govern what happens when the parent changes.
What does MATCH PARTIAL specify, and should you use it?
That the non-NULL referencing columns must match the corresponding columns of at least one parent row, with NULLs acting as wildcards. In practice, no: it is the least widely implemented mode — PostgreSQL documents it as not implemented — and the semantics are hard to reason about. Prefer NOT NULL columns or MATCH FULL.
When is the MATCH clause completely irrelevant?
When the foreign key has a single column, or when every referencing column is declared NOT NULL. In both cases there is no way to produce a partially-NULL key, so SIMPLE, PARTIAL and FULL accept and reject exactly the same rows and the clause is pure documentation.

A composite key is a two-line address. MATCH SIMPLE lets you file a form with the street left blank and asks no further questions; MATCH FULL says either leave the whole address blank or fill in every line.

saying these in an interview costs you the question

Assumes a composite key with one NULL column is always checked
Thinks MATCH FULL forbids NULLs in the referencing columns entirely
Believes MATCH FULL is what applies when the clause is omitted
Designs around MATCH PARTIAL without checking engine support
Confuses MATCH with the ON DELETE referential action

In a composite FOREIGN KEY, what does MATCH FULL change compared with the default MATCH SIMPLE?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

Where the clause goes

The three modes

Why the default surprises people

Making the rule explicit

Portability

What this is not

▾common follow-ups4 questions

▾analogy~20 s

▾red flags5 flags

§ context

▾related questions3 questions

▾related topics4 topics

Where the clause goes

The three modes

Why the default surprises people

Making the rule explicit

Portability

What this is not

answer

go deeper

context