What would I use a MATCH SIMPLE foreign key for?

Question

What I understand:

In addition to valid references to the referenced table, a column with a foreign key is also allowed to hold a NULL value. The SQL standard defines several match modes for foreign keys, like MATCH SIMPLE and MATCH FULL. For multi-column foreign keys, MATCH SIMPLE allows a wrong (= not referencing) value in any of the columns as long as at least one column of the foreign key holds a NULL value.

Multi-column foreign keys are rare (because multi-column primary keys are), but I can imagine valid use cases. For example a translation of a blog post might have a primary key (post_id, language_id). Now something that references such a translation (for example which translation a user is currently editing) would have a multi-column foreign key.

What I don't understand:

Why would I ever use MATCH SIMPLE for my foreign key?

In above example it doesn't make sense to have a "currently editing" entry that only references by post_id and not by language_id.

What would be an example where it does make sense?

score 4 · Answer 1 · answered Sep 01 '16 at 11:51

My preference is to avoid nulls in foreign key columns. Make all foreign keys non-nullable. In fact I have foreign key = not null as a standard check in some static analysis scripts I use for validating database designs.

The FULL / SIMPLE behaviour is obscure enough that in my experience even experts don't always get it (I'm an expert and I have to think twice about it!) As ypercube points out, SIMPLE is effectively the default in Microsoft SQL Server but I suspect most people working with that product never even considered it. If one of your goals in database design is to make data comprehensible by and useful to the average user then foreign keys ought to be non-nullable.

ypercubeᵀᴹ · Answer 2 · 2016-09-01T11:04:31.123

For multi-column foreign keys, MATCH SIMPLE allows a wrong (= not referencing) value in any of the columns as long as at least one column of the foreign key holds a NULL value.

Not exactly. All foreign key constraints allow rows to be "wrong" (not referencing) if the row has NULL values. MATCH FULL for example will allow a (NULL, NULL) row in a two-column foreign key. This doesn't make much sense to me either. The only difference is - as you mentioned - that MATCH SIMPLE will allow (post_id_value, NULL), (NULL, language_id_value) rows while MATCH FULL will not.

I've seen some questions in the site where it made (some) sense to have MATCH SIMPLE and one value to be null. The cases had often some denormalized design. In your example it would look like an extra foreign key from the history table, using only one of the two columns:

 (post_id) REFEERNCES post (post_id)

This would allow the history table to reference the translation table when both values are not null and the post table when language_id is null. I'd never use it myself but I've seen it.

Now, leaving these weird cases behind, the two options (MATCH FULL and MATCH SIMPLE) are identical if all the columns are defined with NOT NULL. And since it's often to have non-nullable columns in foreign keys and this setting affects only multiple-column foreign keys, I guess that's why many designers are not usually worried about this setting and leave it as the default of the DBMS (MATCH SIMPLE in Postgres).

So, to your question:

Why would I ever use MATCH SIMPLE for my foreign key?

I can counter-ask:

Why would I ever use NULL for my foreign key columns?

Also note that most DBMS have not implemented all the options provided by the SQL standard. Postgres has not MATCH PARTIAL and SQL Server has no option at all (all foreign keys behave as MATCH SIMPLE).

Renzo · Accepted Answer · 2016-09-01T21:11:35.737

You are asking:

What would be an example where it does make sense?

Here is a very simple example:

create table student(snum integer primary key,
                     name text);
create table course(cnum integer primary key,
                    title text);
create table exam (snum integer references student,
                   cnum integer references course,
                   mark integer check (mark >=1 and mark <= 10),
                   primary key(snum, cnum));
create table info (snum integer,
                   cnum integer,
                   info text,
                   foreign key (snum) references student,
                   foreign key (cnum) references course,
                   foreign key (snum, cnum) references exam match simple,
                   check (snum is not null or cnum is not null));
insert into student values (1, 'john'), (2, 'mary'), (3, 'lucy');
insert into course values (1, 'programming'), (2, 'database');
insert into exam values (1, 1, 10), (1, 2, 8), (3, 1, 6), (3, 2, 9);
insert into info values (1, null, 'info about student 1'), 
                        (null, 2, 'info about course 2'), 
                        (1, 1, 'info about exam of student 1 in course 1');

In this example the table info maintains information about, student, courses and exams, and we want that this information be consistent. Without match simple this is not possible. Note that however the foreign key is checked:

insert into info values (2, 2, 'info about exam of student 2 in course 2');

ERROR: insert or update on table "info" violates foreign key constraint "info_snum_fkey1"
SQL state: 23503
Detail: Key (snum, cnum)=(2, 2) is not present in table "exam".

Finally, note that in the above example you have to specify at least a student or a course;

insert into info values (null, null, 'nonsense information');

ERROR:  new row for relation "info" violates check constraint "info_check"
DETAIL:  Failing row contains (null, null, nonsense information).

What would I use a MATCH SIMPLE foreign key for?

What I understand:

What I don't understand:

3 Answers3