PostgreSQL, MonetDB and MySQL add foreign key to existing table

Question

When I add a foreign key to a table that already has data, what does each of these database management systems do?

Do they analyze each value of the column to confirm it is a value from the referenced table primary key ?

Or do they have some other optimized mechanism ? And if that's the case, what is that mechanism ?

Answer 1

I can't confirm for MonetDB, but in PostgreSQL and MySQL (and most probably on MonetDB too) the answer is yes, they will check every value and will raise an error if the key does not exists on the referenced table.

Notice that the referenced column doesnt need to be the primary key for the referenced table - you can reference any column as a foreign key to the other table.