This is an archived copy of the forum for reference purposes

Deleting a record with foreign key

posted by bense 7 years ago

Hi all

I'm trying to delete a record from a table, but it doesn't work: "This Room can not be deleted because: A House references it".

To describe it in a simple way: I have a table 'houses' and a table 'rooms'. 'rooms' has a foreign key to 'houses' with "ON DELETE NO ACTION". I'm trying do delete a room, not a house. So the message above...i can't understand it. It should be possible to delete the room without the TRUE-flag of delete (), shouldn't it? The house doesn't reference the room, only the room references the house.

As far as I can see, the relationship is discovered as a one-to-one. But in my opinion, this is not really a one-to-one, because a house can have more than one room.

I'm not sure if i don't understand how it works or if it is a bug. Can someone explain it to me?

Thank you very much!

Ben

i think one to one is when primary key for rooms are in the same time foreign key for houses.

posted by mungiu 7 years ago

Yes, in my case, foreign key is the primary key for rooms. So i have to add an auto_increment primary key to rooms and it will work?

I will try it as soon as possible.

Thanks for your help

posted by bense 7 years ago

yes, that is call one-to-many

posted by mungiu 7 years ago

Hmmm, I added an auto increment id as a primary key (at the rooms table, of course), but i still get the same error. I now have the auto increment id and the foreign key (which still has an unique constraint). Is still something wrong with my table?

posted by bense 7 years ago

give us your db schema from phpmyadmin.

posted by mungiu 7 years ago

Sorry for the delay. Here is a part of my schema:

CREATE TABLE IF NOT EXISTS `houses` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `description` text,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `rooms` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `house_id` int(10) unsigned NOT NULL,
  `content` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `house_id_unique` (`house_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

ALTER TABLE `rooms`
  ADD CONSTRAINT `rooms_ibfk_1` FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

Any suggestions why it doesn't work?

posted by bense 7 years ago

At the first look UNIQUE KEY house_id_unique (house_id) this is the problem if you want many rooms to a house how can be this unique?

posted by mungiu 7 years ago

Yes ok, you are right. My example is not a one-to-many. I will try to delete the unique key.

But another question. If I want a one-to-one relationship, let's say there may be only one door in one room. But the relationship isn't that strong, so it should be possible to delete the door without deleting the room. How can this be achieved?

posted by bense 7 years ago

If you want a one to one relationship you should add it to the parent, not the child. One-to-one means there is only ever one door to a room, which may not be the case, so your example is bad. But let's say hypothetical parent X only ever has one Y, then X should have y_id (unique, NULL allowed) which references y(id) and when creating the constraint you should have ON DELETE SET NULL.

Assuming a room only ever did have one door, it would be like this -- this is pseudo postgres schema, but same logic applies.

rooms (
   id serial primary key,
   door_id int unique references doors(id) ON DELETE SET NULL ON UPDATE CASCADE
   ...
);

door (
   id serial primary key,
   ... 
);

posted by mattsah 7 years ago

I tried it and it works as you said.

Thanks for your help!

posted by bense 7 years ago