-1

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?

13
  • Unclear. Add CREATE TABLE for both tables into the question. – Akina Jan 6 '20 at 20:15
  • Updated my question – Gustavo Lopes Jan 6 '20 at 20:25
  • 1
    Are you looking for ON DELETE CASCADE`? – Vérace Jan 6 '20 at 20:29
  • The root of a problem is clear. The index on F(fk_1, fk_2) was NOT defined as UNIQUE. – Akina Jan 6 '20 at 20:32
  • 1
    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 6 '20 at 21:19
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.