1

I have a problem with my DB (InnoDB). I have two tables:

    table1{
    id1 INT..
    ..
    PRIMARY KEY (id1)
    }

    table2{
    fk INT..
    ...
    FOREIGN KEY (FK) REFERENCES table1(id1)
        ON DELETE CASCADE
        ON UPDATE CASCADE
    }

Now...when I change a record into table1 the records in table2 are deleted! I don't want this behavior. How can I change this?

I read that "InnoDB allows a foreign key constraint to reference a non-unique key". My key is unique. How can I change this?

Thank you!

Marco Peca
  • 81
  • 1
  • 6

1 Answers1

0

This is the expected behaviour since the foreign key in table2 is defined with the ON DELETE CASCADE property. Deletes in table1 are "cascaded to" table2.

You may want to change this definition with ON DELETE SET NULL.

RandomSeed
  • 29,301
  • 6
  • 52
  • 87
  • Yes. I know, but the row will be delete also when I update in table1. I don't know why. – Marco Peca Jun 12 '13 at 09:36
  • Oh I see, sorry I read too fast. Then there must be something else taking place (a trigger on `table1` ?). What happens here cannot be related to the foreign key (unless you use `REPLACE` instead of `UPDATE` to "change a record in `table1`", since `REPLACE` is actually a `DELETE` followed by an `INSERT`). – RandomSeed Jun 12 '13 at 09:37
  • Yes. A trigger. This trigger change a number into another table. No...I use UPDATE – Marco Peca Jun 12 '13 at 09:40
  • Follow this lead! Perhaps this trigger triggers another trigger in cascade. – RandomSeed Jun 12 '13 at 09:44
  • No. I have other triggers but it's impossible that this are triggered. Because into my trigger I update a single row into table3, and there isn't any trigger for my table3. – Marco Peca Jun 12 '13 at 09:48
  • There [**must** be something else](http://sqlfiddle.com/#!2/1f8ac/2). Does this happen even if you issue the `UPDATE` statement directly from the console? – RandomSeed Jun 12 '13 at 09:57
  • I have a lot of tables linked each other. So it's difficult to explain all the DB. Imagine a table for bills, another table for each row of the bill. – Marco Peca Jun 12 '13 at 10:16
  • There is a debugger into mysql? I would see all my queries. It's possible? – Marco Peca Jun 14 '13 at 14:24
  • No debugger that I know of. The [general query log](http://dev.mysql.com/doc/refman/5.6/en/query-log.html) logs all incoming queries (but will not log triggers activity). – RandomSeed Jun 14 '13 at 14:32
  • Ok. Anyway I have found the problem. Is not an sql problem but other queries into PHP! Thank you! – Marco Peca Jun 14 '13 at 14:44