有没有26个外键的MySQL表替代品
I have an InnoDB MySQL database with a table that needs to be able to connect to one of 26 other tables via a foreign key. Each record will only connect to one of these 26 at a time. The table will probably consist of no more than 10,000 records. Is there an alternative way to do this?
-- -----------------------------------------------------
-- Table `db_mydb`.`tb_job`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `db_mydb`.`tb_job` (
`job_id` INT(11) NOT NULL AUTO_INCREMENT ,
// Removed 26 other fields that the table requires
`job_foreignkey_a_id` INT(11) NULL DEFAULT NULL ,
`job_foreignkey_b_id` INT(11) NULL DEFAULT NULL ,
`job_foreignkey_c_id` INT(11) NULL DEFAULT NULL ,
// Removed the other 23 foreign keys fields that are the same
PRIMARY KEY (`job_id`) ,
CONSTRAINT `fka_tb_job_tb`
FOREIGN KEY (`job_foreignkey_a_id` )
REFERENCES `db_mydb`.`tb_foreignkey_a` (`foreignkey_a_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fkb_tb_job_tb`
FOREIGN KEY (`job_foreignkey_b_id` )
REFERENCES `db_mydb`.`tb_foreignkey_b` (`foreignkey_b_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fkc_tb_job_tb`
FOREIGN KEY (`job_foreignkey_c_id` )
REFERENCES `db_mydb`.`tb_foreignkey_c` (`foreignkey_c_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
// Removed the other 23 foreign keys constraints that are the same
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
CREATE INDEX `fka_tb_job_tb` ON `db_mydb`.`tb_job` (`job_foreignkey_a_id` ASC) ;
CREATE INDEX `fkb_tb_job_tb` ON `db_mydb`.`tb_job` (`job_foreignkey_b_id` ASC) ;
CREATE INDEX `fkc_tb_job_tb` ON `db_mydb`.`tb_job` (`job_foreignkey_c_id` ASC) ;
// Removed the other 23 foreign keys indexes that are the same
我有一个InnoDB MySQL数据库，其中一个表需要能够通过一个表连接到其他26个表之一 外键。 每条记录一次只能连接到这26条记录中的一条。 该表可能包含不超过10,000条记录。 有替代方法吗？ p>
- ------------------------- ----------------------------
--表`db_mydb``tb_job`
-- ------ -----------------------------------------------
CREATE TABLE 如果不是EXISTS`db_mydb``tb_job`（
`work_id` INT（11）NOT NULL AUTO_INCREMENT，
//删除了表所需的26个其他字段
`work_foreignkey_a_id` INT（11）NULL DEFAULT NULL ，
`work_foreignkey_b_id` INT（11）NULL DEFAULT NULL，
`work_foreignkey_c_id` INT（11）NULL DEFAULT NULL，
//删除了其他23个相同的外键字段
PRIMARY KEY（` job_id`），
CONSTRAINT`fka_tb_job_tb`
FOREIGN KEY（`job_foreignkey_a_id`）
REFERENCES`db_mydb``tb_foreignkey_a`（`foreignkey_a_id`）
ON DELETE NO ACTION
ON UPDATE NO ACTION，\ n CONSTRAINT`fkb_tb_job_tb`
FOREIGN KEY（`job_foreignkey_b_id`）
参考`db_mydb``tb_foreignkey_b`（`foreignkey_b_id`）
ON DELETE NO ACTION
ON UPDATE NO ACTION，
CONSTRAINT `fkc_tb_job_tb`
FOREIGN KEY（`job_foreignkey_c_id`）
REFERENCES`db_mydb``tb_foreignkey_c`（`foreignkey_c_id`）
ON DELETE NO ACTION
ON UPDATE NO ACTION）
//删除了其他23个外键 相同的约束
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
CREATE INDEX`fka_tb_job_tb` ON`db_mydb``tb_job`（`job_foreignkey_a_id`ASC）;
创建索引`fkb_tb_job_tb` ON`db_mydb `.`tb_job`（`job_foreignkey_b_id`ASC）;
创建索引`fkc_tb_job_tb` ON`db_mydb``tb_job`（`job_foreignkey_c_id`ASC）;
//删除了其他23个相同的外键索引
code> pre>
div>
This is the problem of generic foreign keys, which MySQL and friends tend not to support. There are two ways you can do this.
The first, as you have done, is nullable foreign keys, one for every type.
The other, as in Django's Content Types, is to have a join table, each row having a row id and a field that specifies the table to look up on. Your code then has to formulate the SQL query depending on the contents of the field. It works well, but has limitations:
The downside of the first one is bloat, but it brings you the upsides of normal FKs, i.e. referential integrity and SQL joins etc, both of which are very valuable. You can't get those with the second method.
Depends if you want to maintain foreign key constraint, you can have one table that references one of the tables by a key or table type. Problem is you will loose the foreign key constraint. Of course, if you can create a function based constraint, then it can work for you. Or you can enforce the relationship using a trigger. Function based constraints are not available in mysql.
Yes, you can do that. These two StackOverflow answers illustrate the underlying principles in a slightly different context.
- Same data from different entities in Database - Best Practice - Phone numbers example
- Different user types / objects own content in same table - how?
Using MySQL, you'll need to replace critical CHECK() constraints with foreign key references. This doesn't work in the most general case for MySQL, but it does work in this particular application.
If this isn't enough information to get you going, leave me a comment, and I'll try to expand this answer a little more.