2

This question already has an answer here:

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?

marked as duplicate by Evan Carroll, Vérace, Marco, Paul White Jan 11 at 9:42

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1

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).
  • Ah, this didn't occur to me because I would have given exam a surrogate key exam_id that the info can reference, but yes, this seems to be the correct answer. – AndreKR Sep 2 '16 at 2:55
3

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.

1

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).

  • Well, (NULL, NULL) totally makes sense to me, as it means "currently not referencing any row". It seems so normal to me that I omitted it from my description of MATCH. – AndreKR Sep 1 '16 at 11:03
  • If (NULL, NULL) makes sense (eg we have a history fact that we don't know which post or language it refers to), then (post,NULL) makes sense, too, doesn' it? (a history fact that we know which post it refers to but not which language). – ypercubeᵀᴹ Sep 1 '16 at 11:06
  • Oh, you mean in this concrete example... no, here it doesn't really make sense. Maybe I should come up with an example where it does, just a sec. – AndreKR Sep 1 '16 at 11:10
  • Ok, I changed it to from history entries to "which translation a user is currently editing", that makes sense to be NULL. Sorry to change the question, but it was never really about the sense of NULL but about the sense of MATCH SIMPLE. – AndreKR Sep 1 '16 at 11:16
  • 2
    Perhaps one answer is that SQL was designed by a diverse committee of commercially interested parties. Making sense hasn't always been top of their agenda. – nvogel Sep 1 '16 at 11:53

Not the answer you're looking for? Browse other questions tagged or ask your own question.