My Profile Photo

Cory Kelly

CoryPlusPlus - Cory with classes

A blog documenting solutions to some interesting problems

Foreign Key on Non Primary Column

Recently I encountered an exception that was thrown in our production environment when creating a new foriegn key constraint using liquibase on a mysql db. I was perplexed because the upgrade was successful in both our development and staging environments. After debugging to make sure the database settings were the same in production (verify column types were the same, verify that the database engine the same on all columns)

The foreign key constraint was mapped to a column that was not a primary key BUT had a unique constraint on it. This gave me a false sense of security and as it turned out the unique constraint did not prevent against multiple NULL values in that column. The data ended up being the variable factor between environments, and the lesson learned is to never map a foreign key to a non primary column!

comments powered by Disqus