MySQL table with only varchar as foreign key

Question

I have a table with a single unique VARCHAR field (512). I want an external reference to this first table in another table. Both tables use InnoDB. If I add the VARCHAR key (512) to the second table and add the foreign key constraint, will the 512-byte data be stored twice?

If so, is there a way to keep only a reference to the index, and not to varchar itself?

In short, my question is, is there an efficient way in InnoDB to keep foreign keys in long VARCHAR fields?

Thank you very much,

Yaniv

Answer 1

Yes, if there is a VARCHAR(512) column in the link table, the data will exist twice.

I recommend that you force the foreign key of the link table to refer to the whole primary key for the first table, and not to 512-byte data. This is a kind of normalization.

Answer 2

I conducted a simple test: create 3 tables, one for storing the data itself with two columns and ID (int) and data (varchar [120]), another table that uses the identifier as a foreign key and the last one that uses data as a foreign key:

 CREATE TABLE `dados` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(120) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) USING BTREE, KEY `idx` (`id`,`name`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `refINT` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `dado` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `id` (`dado`), CONSTRAINT `id` FOREIGN KEY (`dado`) REFERENCES `dados` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `refSTR` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `dado` varchar(120) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), KEY `nome` (`dado`), CONSTRAINT `nome` FOREIGN KEY (`dado`) REFERENCES `dados` (`name`) ON DELETE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; 

Insert 100 records into each table and compare the size of the final table:

 dados: 192.0 KB refINT: 32.0 KB refSTR: 32.0 KB 

So, I think the data is NOT replicated to the varchar foreign key, well, at least in MySQL 5.1.

Answer 3

Keeping the key size small is always good. You can solve the problem with a large VARCHAR index by instead getting the extra checksum column that you generate during insertion. This checksum field will contain the output of the hash algorithm CRC32() or maybe MD5() larger column.