MySQL: varchar foreign key pitfalls

Why VACHAR as Foreign Key might be needed

Imagine that you have two tables BOOKS & GENRES.
You need to have genre column into your BOOKS which would be one from your GENRES

Depending of which data either genre_id or genre_name u'll keep into your new column, your BOOKS table may look like one of the following:

* Genre Id as FK

id *(pk)	title	author	genre_id *(fk)
1	The Little Prince	Saint-Exupéry	1
2	Fear and Loathing in Las Vegas	Hunter Thompson	2

Or:

* Genre Type as FK

id *(pk)	title	author	genre_type *(fk)
1	The Little Prince	Saint-Exupéry	FICTION
2	Fear and Loathing in Las Vegas	Hunter Thompson	ALTERNATIVE

The second table is obviously more readable & understandable.
To make it happen we have to make genre the primary key of our GENRES and use VARCHAR as foreign key in our BOOKS.

* Genre Type as PK

type *(pk)
FICTION
ALTERNATIVE

Watchout: Better Do not use Type as a Name at the same time otherwise if the name will be changed it could has a bad impact.

When you're trying to use VARCHAR as your Foreign Key, you may face with the following exception: MySQL:ERROR 1215 Cannot add foreign key constraint ##To add VACHAR as Foreign Key - **Engine** should be the same *e.g. InnoDB* - **Datatype** should be the same, and with same length. *e.g. VARCHAR(20)* - **Collation** Columns charset should be the same. *e.g. utf8* *Watchout:* Even if your tables have same Collation, columns still could have different one. - **Unique** - Foreign key should refer to field that is unique (usually private key) in the reference table. ---- ##Changeset Create `GENRES` table: CREATE TABLE IF NOT EXISTS `GENRES` ( `type` varchar(40) NOT NULL DEFAULT '', PRIMARY KEY (`type`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `PLATFORMS` (`type`) VALUES ('FICTION'); INSERT INTO `PLATFORMS` (`type`) VALUES ('ALTERNATIVE'); Add `genre_type` into `BOOKS` table: ALTER TABLE `BOOKS` ADD COLUMN `genre_type` VARCHAR(40) NOT NULL DEFAULT 'FICTION' AFTER `author`; ALTER TABLE `BOOKS` ADD INDEX `FK_books_genres_type_idx` (`genre_type` ASC); ALTER TABLE `BOOKS` ADD CONSTRAINT `FK_books_genres_type` FOREIGN KEY (`genre_type`) REFERENCES `PLATFORMS` (`type`); ---- ## see Also * [Transactions Control in SQL](http://bloomlab.blogspot.com/2015/03/transactions-control-in-sql-postgresql.html) * [SQL data reliability - CONSTRAINTS usage](http://bloomlab.blogspot.com.ee/2015/03/sql-data-reliability-constraints-usage.html) * [PostgreSQL Date & Time](http://www.tutorialspoint.com/postgresql/postgresql_date_time.htm) * [Many To One relation](http://levelup.lishman.com/hibernate/associations/many-to-one.php)