Mysql - How to delete a row with foreign key referencing multiple rows

Question

I have a table F:

CREATE TABLE IF NOT EXISTS `mydb`.`table_f` (
  `id_f` INT NOT NULL AUTO_INCREMENT,
  `fk_1` INT(11) NOT NULL,
  `fk_2` INT(11) NOT NULL,
  `column_F` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id_f`),
  INDEX `index2` (`fk_1` ASC, `fk_2` ASC) )
ENGINE = InnoDB

id_f | fk_1  | fk_2  | column_F
-------------------------------
1    | 1     | 1     | AA
2    | 1     | 2     | BB
3    | 1     | 2     | CC  <-- "Duplicate" of row id_f=2 (but valid data)
4    | 2     | 1     | DD

And a table A:

CREATE TABLE IF NOT EXISTS `mydb`.`table_a` (
  `id_a` INT NOT NULL AUTO_INCREMENT,
  `fk_1` INT(11) NOT NULL,
  `fk_2` INT(11) NOT NULL,
  PRIMARY KEY (`id_a`),
  INDEX `a_idx` (`fk_1` ASC, `fk_2` ASC) ,
  CONSTRAINT `fk`
    FOREIGN KEY (`fk_1` , `fk_2`)
    REFERENCES `mydb`.`table_f` (`fk_1` , `fk_2`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

id_a  | fk_1 | fk_2
---------------------
1     | 1    | 1
2     | 1    | 2
3     | 2    | 1

Table A has a foreign key as: [fk_1, fk_2], so I ensure that there is, at least, one record in Table F.

The problem is, I want to delete from table_f where id_f=3. But I cannot, because of the foreign key.

Also, important to notice, the duplicate on table_f is on purpose and necessary.

How can I ensure that a table has at least one row on another table, but being able to delete this row later?

Unclear. Add CREATE TABLE for both tables into the question. — Akina, Jan 06 '20 at 20:15
The root of a problem is clear. The index on F(fk_1, fk_2) was NOT defined as UNIQUE. — Akina, Jan 06 '20 at 20:32
I don't want to delete anything on table_a, just on table_f. Even after deleting on table_f, table_a will continue to have its foreign key — Gustavo Lopes, Jan 06 '20 at 20:33
@Akina it's not supposed to be UNIQUE. I can have more than one row on table_f. The data provided in the question is valid. I just cannot delete a row on table_f (but I can insert seamlessly) — Gustavo Lopes, Jan 06 '20 at 20:35
If so you will have this problem each time when you need to delete a record. — Akina, Jan 06 '20 at 20:41
From the other side, if F(fk_1, fk_2) must NOT to be unique then your data is not normalized. — Akina, Jan 06 '20 at 20:42
No, it's not. But I really don't know how to do that with my data, which involves arrays of specific types (saved on other 2 tables). And it's supper complex and big that I can't make it small enough to ask on StackExchange. I'd really appreciate if you could help me. — Gustavo Lopes, Jan 06 '20 at 20:48
Looks like an X-Y problem to me. You seem to misunderstand how referential integrity constraints work. — mustaccio, Jan 06 '20 at 20:50
No, it's not. Normalized reference must be deterministic. Non-unique index can NOT provide deterministic reference. — Akina, Jan 06 '20 at 20:53
@Akina I created the question about my real problem. Probably it'll be flagged to be deleted, but if you have a spare time and could help me, this is the question — Gustavo Lopes, Jan 06 '20 at 21:15
The way to normalizing is in moving (fk_1, fk_2) pair into separate table, where this pair is defined as UNIQUE. And both A and F references to it. — Akina, Jan 06 '20 at 21:19

Akina · Accepted Answer · 2020-01-06T21:21:01.597

You may disable FKs (SET FOREIGN_KEY_CHECKS=0;), delete record, then enable FKs back. But it is too unsafe - this setting affects to all. Less (slightly) unsafe is SET SESSION FOREIGN_KEY_CHECKS=0;.

You may remove FK, delete record, then restore FK. fiddle. Pay attention - CONSTRAINT symbol is used. It is less unsafe.

Or you may edit the record(s) which references to duplicated records. See fiddle. Pay attention - only one record must be deleted. It is minimal unsafe.

In both variants - deleting wrong (unique) record will forbid re-create FK or re-assign referential fields.

UPDATE:

The most safe method (it seems) is to use EVENT which sets SET SESSION FOREIGN_KEY_CHECKS=0; and deletes the record. This guarantees that no interference occured (because it is executed in separate session), except some DELETE trigger on table F exists.

Nevertheless if you delete wrong (unique) record you'll have a problem...

Mysql - How to delete a row with foreign key referencing multiple rows

1 Answers1

Linked