无法定义外键 [英] Can't define a foreign key

问题描述

在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.

推荐答案

你试图做的事情让我感到困惑。

我理解它，你试图用复杂的方式做一件简单的事情，而且效率也很低。

据我所知， id 是一个主要的key和来自另一个表的外键暗示该表可以为另一个表的每一行设置0或1行。

单个表如：

MonitorGroups：

Id：INT PRIMARY KEY NOT NULL AUTOINCREMENT

名称：VARCHAR（50）

GroupId：INT NULL

会基本相同，但更容易处理。



我认为你应该提高你的数据库设计技能。

What you try to do is rather confuse to me.
As I understand it, you try to do a simple thing the complicated way, and inefficient too.
As I understand it, the id that is a primary key and a foreign key from another table imply the the table can have 0 or 1 row for every row of the other table.
A single table like:
MonitorGroups:
Id: INT PRIMARY KEY NOT NULL AUTOINCREMENT
Name: VARCHAR(50)
GroupId: INT NULL
would basically the same, but simpler to handle.

I think you should improve your skills about database design.

这篇关于无法定义外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！