Can a foreign key reference a non-unique index?

Question

I thought a foreign key meant that a single row must reference a single row, but I'm looking at some tables where this is definitely not the case. Table1 has column1 with a foreign key constraint on column2 in table2, BUT there are many records in table2 with the same value in column2. There's also non-unique index on column2. What does this mean? Does a foreign key constraint simply mean that at least one record must exist with the right values in the right columns? I thought it meant there must be exactly one such record (not sure how nulls fit in to the picture, but I'm less concerned about that at the moment).

update: Apparently, this behavior is specific to MySQL, which is what I was using, but I didn't mention it in my original question.

Answer 1

From MySQL documentation:

InnoDB allows a foreign key constraint to reference a non-unique key. This is an InnoDB extension to standard SQL.

However, there is a pratical reason to avoid foreign keys on non-unique columns of referenced table. That is, what should be the semantic of "ON DELETE CASCADE" in that case?

The documentation further advises:

The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined (...) You are advised to use foreign keys that reference only UNIQUE (including PRIMARY) and NOT NULL keys.

Answer 2

Your analysis is correct; the keys don't have to be unique, and constraints will act on the set of matching rows. Not usually a useful behavior, but situations can come up where it's what you want.