mysql如何自动更新外键

Question

这是我的 table

 CREATE TABLE IF NOT EXISTS `carslibrary` (
      `CarID` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `CarName` varchar(255) NOT NULL,
      PRIMARY KEY (`CarID`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

    CREATE TABLE IF NOT EXISTS `colorslibrary` (
      `ColorID` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `ColorName` varchar(255) NOT NULL,
      PRIMARY KEY (`ColorID`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;


    CREATE TABLE IF NOT EXISTS `facerecord` (
      `carslibrary_ID` int(10) unsigned NOT NULL,
      `colorslibrary_ID` int(11) unsigned NOT NULL,
      KEY `carslibrary_ID` (`carslibrary_ID`),
      KEY `colorslibrary_ID` (`colorslibrary_ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

我注意到当我在 carslibrary 表中添加汽车记录时，facerecord 表中的 carslibrary_ID 属性不会自动更新，我应该怎么做才能做到这一点？

Answer 1

首先，您需要为 facerecord.colorslibrary_ID 指定一个默认值因为在插入 carslibrary 时您不会“知道”它是什么 table 。那就是说您可以更改 facerecord 的 DDL表是:

CREATE TABLE `facerecord` (
`carslibrary_ID` int(10) unsigned NOT NULL,
`colorslibrary_ID` int(10) unsigned NOT NULL DEFAULT '0',
KEY `carslibrary_ID` (`carslibrary_ID`),
KEY `colorslibrary_ID` (`colorslibrary_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

我还更改了 colorslibrary_ID 的数据类型列以匹配 colorslibrary.ColorID 的列专栏以防您想要在 facerecord.colorslibrary_ID 之间设置外键和 colorslibrary.ColorID ;).为了完整起见，您应该在 colorslibrary 中插入一行ColorID = 0 的表。因此:

insert into `colorslibrary` (ColorName) values ('unknown color');

update `colorslibrary` set ColorID = 0 where ColorName = 'unknown color';

然后您可以继续定义要插入到 facerecord 中的触发器表:

delimiter $$

CREATE TRIGGER carslibrary_trigger
    AFTER insert ON carslibrary
    FOR EACH ROW
    BEGIN
      insert into facerecord (carslibrary_ID) values (new.CarID);
END$$

delimiter;

插入到 facerecord 中的所有新行然后将插入带有 colorslibrary_ID 的表格与“未知颜色”有关colorslibrary.ColorName .然后您可以手动更新 facerecord.colorslibrary_ID当你知道的时候。

祝你好运!

PS 如果您需要删除任何现有的 AFTER insert从 carslibrary 表中获取触发器，您可以首先找到现有的触发器:

select trigger_name
from information_schema.triggers
where event_object_table = 'carslibrary'
and action_timing = 'AFTER'
and event_manipulation= 'INSERT';

然后获取上述语句返回的触发器的名称(假设返回字符串 'carslibrary_trigger')并运行:

drop trigger carslibrary_trigger;

然后重新运行 CREATE TRIGGER脚本。

设置触发器后，它会在您指定的触发器操作发生时自动执行您指定的操作。在这种情况下，我们告诉数据库“在 carslibrary 表中发生插入后，自动将一行插入到 facerecord 表中，使用新 CarID 行的 carslibrary 填充 facerecord.carslibrary_ID 列”。与大多数事情一样，最好的方法就是尝试一下!手动创建触发器后，将新行插入“carslibrary table. Now look at the data in the” facerecord` 表 - 您应该会看到触发器触发时插入的新行。

听起来您会从学习触发器中受益。我推荐 MySQL site 上的文档因为这个答案比我最初预期的要长!