SQL - Foreign key constraints and cardinality

Question

Can someone explain foreign key constraints and cardinality? I am reading the SQLite manual and I am at the section on advanced foreign key constraint features. In its explanation it says:

Parent and child keys must have the same cardinality. In SQLite, if any of the child key columns (in this case songartist and songalbum) are NULL, then there is no requirement for a corresponding row in the parent table.

I know that cardinality is supposed to be the number of values in a set, or at least it is in math, so I assume if I have ten person records each with a column sex then there are two possibilities male and female so assuming there are some males and some females the cardinality is 2... right?

I searched on google and didn't find much. The closest thing I found was a Microsoft page for Visio 2003 that has a section explaining "About attributes, referential integrity, and cardinality". It says:

The cardinality of a relationship describes how many records in a parent table can be directly related to records in a child table. Cardinality is expressed in terms such as one-to-one, one-to-many, many-to-one, or many-to-many.

That's probably a pretty good description but I still don't understand what SQLite means when it says the parent and child keys must have the same cardinality. If you could give some examples in your answer that would also be helpful. Thanks

It means exactly what you posted. You are overthinking this. Just remember the last sentence of the second gray box in your post. — Dan Bracuk, Jun 05 '14 at 22:32

score 5 · Accepted Answer · answered Jun 06 '14 at 07:04

This is the example's table structure:

CREATE TABLE album(
  albumartist TEXT,
  albumname TEXT,
  albumcover BINARY,
  PRIMARY KEY(albumartist, albumname)
);

CREATE TABLE song(
  songid     INTEGER,
  songartist TEXT,
  songalbum TEXT,
  songname   TEXT,
  FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname)
);

The parent table (album) has a composite primary key, i.e., the primary key consists of two columns.

So if the child table wants to refer to an album, it has to use two columns, too.

In this case, both the parent and child keys have a cardinality of two.

SQL - Foreign key constraints and cardinality

1 Answers1