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.
© 2024, Oracle Corporation and/or its affiliates
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.