Click here to Skip to main content
15,882,113 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted
Updated 4-Jun-18 8:47am
Comments
[no name] 3-Jun-18 6:36am    
"I'm not sure whether MySQL support nullable foreign keys": MySQL does Support nullable foreign keys.
Check again your table Monitors and make sure that GroupId is NULL for every record.

If it does not helps, start again from scratch in a new DB to check your table creation process.
ilostmyid2 9-Jun-18 8:46am    
no help. existence of a row in table MonitorGroups with Id 0 is inevitable for creating the foreign key.
[no name] 9-Jun-18 8:53am    
Id == '0' is not the same like Id == NULL

NULL: means not defined
0: means a valid integer number

1 solution

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.
 
Share this answer
 
Comments
ilostmyid2 9-Jun-18 8:53am    
I see that you didn't get the architecture. Please re-read what I wrote. Id of a row in MonitorGroups table is a reference for a row in Monitors table. For example, if we want to know what monitors are defined in a specified group we run this query:
SELECT * FROM Monitors WHERE GroupId=<id>;
[no name] 9-Jun-18 14:47pm    
"I see that you didn't get the architecture":
Cool down! It is you who does not get the Point for foreign-prim key stuff. It is such a Basic, easy stuff, which can be figured out easy with a Minimum of effort!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900