mysql - 来自单父表的两个外键约束不起作用

Question

我正在尝试从单个父表创建两个具有两个外键约束的表。

这两个父子表有什么问题吗？

我的数据库中只有这两个表(我删除了所有其他表):

-- -----------------------------------------------------
-- Table `db_test`.`tbl_test_monitoring_type`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `db_test`.`tbl_test_monitoring_type` (
  `id` SMALLINT NOT NULL AUTO_INCREMENT COMMENT 'Synthetic ID for an Order Status.',
  `monitoring_type` VARCHAR(1) NOT NULL,
  `message_type` VARCHAR(1) NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `creared_by` VARCHAR(10) NULL COMMENT 'staff PK number',
  `modified_by` VARCHAR(10) NULL COMMENT 'staff PK number',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `ukidx_monitoring_type` (`monitoring_type` ASC, `message_type` ASC))
ENGINE = InnoDB
COMMENT = 'Lookup table for storing monitorying types.';

-- -----------------------------------------------------
-- Table `db_test`.`tbl_test_monitoring`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `db_test`.`tbl_test_monitoring` (
  `id` SMALLINT NOT NULL AUTO_INCREMENT COMMENT 'Synthetic ID for an Order Status.',
  `monitoring_type` VARCHAR(1) NOT NULL,
  `message_type` VARCHAR(1) NOT NULL,
  `date` TIMESTAMP NOT NULL COMMENT 'Date of monitoring recorded.',
  `message` VARCHAR(500) NULL COMMENT 'More information attached by the user.' ,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `ukidx_monitoring_type_message_type` (`monitoring_type` ASC, `message_type` ASC),
  CONSTRAINT `fk_test_message_type_monitoring`
    FOREIGN KEY (`message_type`)
    REFERENCES `db_test`.`tbl_test_monitoring_type` (`message_type`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'Table to store complete information about monitoring';

我在第二个外键约束中遇到问题。 IE。如果我添加第二个外部，则不会创建表 tbl_test_monitoring。第二个约束

CONSTRAINT `fk_test_message_type_monitoring`
    FOREIGN KEY (`message_type`)
    REFERENCES `db_test`.`tbl_test_monitoring_type` (`message_type`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

这两个是两个数据库，依赖于任何现有的数据库。我尝试删除所有其他表，即使出现上述错误。

Answer 1

引用的列需要建立索引。尝试这样:

CREATE TABLE IF NOT EXISTS `tbl_test_monitoring_type` (
  `id` SMALLINT NOT NULL AUTO_INCREMENT COMMENT 'Synthetic ID for an Order Status.',
  `monitoring_type` VARCHAR(1) NOT NULL,
  `message_type` VARCHAR(1) NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `creared_by` VARCHAR(10) NULL COMMENT 'staff PK number',
  `modified_by` VARCHAR(10) NULL COMMENT 'staff PK number',
  PRIMARY KEY (`id`),
  INDEX idx_messagetype (message_type),
  UNIQUE INDEX `ukidx_monitoring_type` (`monitoring_type` ASC, `message_type` ASC))
ENGINE = InnoDB
COMMENT = 'Lookup table for storing monitorying types.';