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
• SQL data reliability - CONSTRAINTS usage
• PostgreSQL Date & Time
• Many To One relation