Bug #8246 Ability to have a static foreign key field
Submitted: 1 Feb 2005 18:52 Modified: 13 May 2010 16:03
Reporter: Andrew Hanna Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version: OS:
Assigned to: CPU Architecture:Any
Triage: D5 (Feature request)

[1 Feb 2005 18:52] Andrew Hanna
Description:
Would it be possible to have static fields in a foreign key?

How to repeat:
CREATE DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `type` (
`type` VARCHAR( 20 ) NOT NULL ,
`name` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `type` )
) TYPE=INNODB;

CREATE TABLE `table1` (
`type` VARCHAR( 20 ) NOT NULL ,
`id` VARCHAR( 20 ) NOT NULL ,
`data` VARCHAR( 50 ) NOT NULL ,
FOREIGN KEY (`type`) REFERENCES `type` (`type`) ON UPDATE CASCADE ON DELETE RESTRICT,
PRIMARY KEY ( `type` , `id` )
) TYPE=INNODB;

CREATE TABLE `table2` (
`id` VARCHAR( 20 ) NOT NULL,
`name` VARCHAR ( 50 ) NOT NULL,
PRIMARY KEY (`id`)
) TYPE=INNODB;

Suggested fix:
I would want to be able to add a foreign key to table2 like this:

ALTER TABLE `table2` ADD FOREIGN KEY ('test', `id`) REFERENCES `table1` (`type`, `id`) ON UPDATE CASCADE ON DELETE RESTRICT;

INSERT INTO `type` VALUES ('test', 'test');

INSERT INTO `table1` VALUES ('test', 'first', 'data'), ('test', 'second', 'data');

INSERT INTO `table2` VALUES ('first', 'first name'), ('second', 'second name');

The idea being that table2 is always a certain type, but might be different from table3 ... tableN.  This way specific data can be stored for specific types, all the while being properly linked through foreign keys.

This *IS* currently possible by adding the `type` field to table2, but in this case `type` will always be the same, so the field is using unncessary space...

I don't know if this is a good idea, but I feel that I could use it in my projects.