问题描述
在MySQL中我有两张桌子:
监视器:
Id:INT PRIMARY KEY NOT NULL AUTOINCREMENT
GroupId:INT NULL
....
MonitorGroups:
Id:INT PRIMARY KEY NOT NULL AUTOINCREMENT
名称:VARCHAR(50)
我需要定义这个约束:
ALTER TABLE监视器ADD CONSTRAINT monitor_group_fk FOREIGN KEY(GroupId)REFERENCES MonitorGroups(Id);
但我收到以下错误:
ERROR 1452(23000):无法添加或更新子行:外键约束失败(`zm`。#sql-475_1c237`,CONSTRAINT` monitor_group_fk` FOREIGN KEY( `GroupId`)REFERENCES`MonitorGroups`(`Id`))
目前MonitorGroups表中没有记录,表的行的GroupId没有设置Monitors。我需要它是合法的。换句话说,我需要MySQL来跟踪表Monitor的字段GroupId,只有它被设置。我想将约束定义为:
ALTER TABLE监视器ADD CONSTRAINT monitor_group_fk FOREIGN KEY(GroupId)REFERENCES MonitorGroups(Id)ON UPDATE CASCADE ON DELETE SET NULL;
因此,如果在表MonitorGroups中更改了组ID(Id字段),MySQL会在表监视器中自动更改它,如果从表MonitorGroups中删除了一个组,MySQL会自动设置表中对应行的GroupId监视为null。怎么了?怎么做到这个目标?
我尝试过的事情:
我调查了可以为空的外键,但最后找不到解决方案。我不确定MySQL是否支持可以为空的外键。
In MySQL I've two tables:
Monitors:
Id: INT PRIMARY KEY NOT NULL AUTOINCREMENT
GroupId: INT NULL
....
MonitorGroups:
Id: INT PRIMARY KEY NOT NULL AUTOINCREMENT
Name: VARCHAR(50)
I need to define this constraint:
ALTER TABLE Monitors ADD CONSTRAINT monitor_group_fk FOREIGN KEY (GroupId) REFERENCES MonitorGroups (Id);
But I get the following error:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`zm`.`#sql-475_1c237`, CONSTRAINT `monitor_group_fk` FOREIGN KEY (`GroupId`) REFERENCES `MonitorGroups` (`Id`))
At the moment there's no record in MonitorGroups table and GroupId of rows of table Monitors are not set. I need it to be legal. In another words, I need MySQL to keep track of the field GroupId of table Monitor only if it's set. I would like to define the constraint as:
ALTER TABLE Monitors ADD CONSTRAINT monitor_group_fk FOREIGN KEY (GroupId) REFERENCES MonitorGroups (Id) ON UPDATE CASCADE ON DELETE SET NULL;
so that if a group id (Id field) changed in table MonitorGroups, MySQL change it automatically in table Monitors and if a group was deleted from table MonitorGroups, MySQL automatically set GroupId of corresponding rows in table Monitors to null. What's wrong and how to reach this goal?
What I have tried:
I investigated about nullable foreign keys, but couldn't find a solution at last. I'm not sure whether MySQL support nullable foreign keys.
推荐答案
这篇关于无法定义外键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!