Search code examples
mysqlforeign-keysdelete-row

Deleting from table with foreign keys

I'm unsure if I have misunderstood something fundamental here, but here goes:

I have a persons table (persons have the typical attributes for persons: names, address, etc.) and a table of elected representatives. The representatives have additional attributes (e.g a deputy representative/substitute representative or someone they them-self are substitutes for). As I have many people and only a few representatives (n<200) I store all attributes common for all people in the persons table, and only the representatives-spesific data in the representatives-table. All "data" in the representatives is relations to the persons-table. This table just reflect the current status of who are cleared for voting (and who are cleared to substitute form him, or he is substituting for). 

representatives 
(numbers are the pk for persons, and empty cells here are NULL in the db)
_____________________________
|id|has_substitute|is_sub_for|
| 1|           2  |          |
| 2|              |        1 |
| 3|              |          |
| 5|              |          |

So comes the day when a representative get bumped off his voting-privileges, and I need to delete him from the representatives, but not in the person-table (he is still a person). The same goes from the people he is a sub for, or people that have been a sub for him. They are FK in my schema, but I do not want to delete them as persons, only the relations to the representative that goes out. Just the row from the representatives table.

DELETE FROM representatives WHERE id=1;

goes all wrong. 'Cannot delete or update a parent row: a foreign key constraint fails' But I do not intend to delete the persons, just the relations created with the 'representatives'-table.

  1. Is there a DELETE FROM and ignore the relationships (just delete the row)-function for mysql?

  2. Am I modeling this wrong? If so, what would be a better way?

PS: Here's the create table info

CREATE TABLE `representatives` (
  `person_id` varchar(33) NOT NULL,
  `permanent_substitute_for_id` varchar(33) DEFAULT NULL,
  `temporarily_substitute_for_id` varchar(33) DEFAULT NULL,
  PRIMARY KEY (`person_id`),
  KEY `representatives_250f5a24` (`permanent_substitute_for_id`),
  KEY `representatives_79c95594` (`temporarily_substitute_for_id`),
  CONSTRAINT `permanent_substitute_for_id_5c64807b` FOREIGN KEY (`permanent_substitute_for_id`) REFERENCES `persons` (`id`),
  CONSTRAINT `person_id_refs_id_5c64807b` FOREIGN KEY (`person_id`) REFERENCES `persones` (`id`),
  CONSTRAINT `temporarily_substitute_for_id_5c64807b` FOREIGN KEY (`temporarily_substitute_for_id`) REFERENCES `persones` (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `persones` (
  `id` varchar(33) NOT NULL,
  `first_name` varchar(150) NOT NULL,
  ..[more stuff]..
  PRIMARY KEY (`id`),
  KEY `fylkesperspektiv_personer_70fdfe4` (`fylke_id`),
  KEY `fylkesperspektiv_personer_3ab19c51` (`parti_id`),
  CONSTRAINT `fylke_id_refs_id_36bce012` FOREIGN KEY (`fylke_id`) REFERENCES `fylkesperspektiv_fylker` (`id`),
  CONSTRAINT `parti_id_refs_id_c381e045` FOREIGN KEY (`parti_id`) REFERENCES `fylkesperspektiv_partier` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Solution

  • I would define a rep_id column for each person and tie that to the rep table:

    ALTER TABLE person
    ADD CONSTRAINT FK_person_rep
    FOREIGN KEY (rep_id) REFERENCES rep(rep_id)
    ON UPDATE CASCADE ON DELETE SET NULL;

    I would also not use the redundant has_substitute column in the rep table and only use is_sub_for and then tie that to the rep_id in the same table.

    ALTER TABLE rep
ADD CONSTRAINT FK_rep_rep
FOREIGN KEY (is_sub_for) REFERENCES rep(rep_id)
ON DELETE CASCADE ON UPDATE CASCADE;

    This way, when a rep gets deleted, all of their constutuent persons' rep_id column gets updated to NULL, and their subs in the same table are deleted. If a rep_id gets changed (not that it should ever happen but just in case), the update is cascaded on its children in both tables.