mysql - 在插入时将唯一记录隐式插入外键表

Question

是否可以添加或删除 type_name来自 type 的条目在 language 中插入记录时的表表？

我收到#1452(外键约束)错误:

Cannot add or update a child row: a foreign key constraint fails (`test`.`language`,    CONSTRAINT `language_ibfk_1`        FOREIGN KEY (`type_name`)        REFERENCES `type` (`type_name`)        ON DELETE CASCADE        ON UPDATE CASCADE)

表架构

CREATE TABLE IF NOT EXISTS `type` (
    `type_name` VARCHAR(128) NOT NULL UNIQUE,
    PRIMARY KEY(`type_name`)
);

CREATE TABLE IF NOT EXISTS `language` (
    `language_id` INT NOT NULL AUTO_INCREMENT,
    `language_name` VARCHAR(256) NOT NULL,
    `type_name` VARCHAR(128) NOT NULL,
    PRIMARY KEY(`language_id`),
    FOREIGN KEY (`type_name`)
        REFERENCES `type`(`type_name`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

插入语句

INSERT INTO `language`(`language_name`, `type_name`) VALUES
('C', 'programming'),
('Java', 'programming'),
('Python', 'scripting'),
('PHP', 'scripting'),
('HTML', 'markup'),
('XML', 'markup');

由于 CASCADE 而隐式插入值.

INSERT INTO `type`(`type_name`) VALUES
('programming'),
('scripting'),
('markup');

Answer 1

如果你真的必须使用这样的解决方案，你可以在插入语言表之前使用触发器:

CREATE TRIGGER trigger_name BEFORE INSERT ON language FOR EACH ROW
BEGIN

    INSERT IGNORE INTO type
            (type_name)
        VALUES
            (NEW.type_name);

END