Good evening, dear users. I am interested in such a question.

Let there be three tables. phones with id, model_id; models: id, name; features: id, model_id. phones.model_id = models.id , features.model_id = models.id . Where to write restrictions on models.id in both the phones and features tables or only in one? And if I write to phones ON DELETE CASCADE , in features ON DELETE RESTRICT , which of the things apply to the models.id field?

    1 answer 1

    Which of the RESTRICT / CASCADE / SET NULL modifiers to set is dependent on your business logic. If the RESTRICT constraint in the list of foreign keys worked, then the entire update / delete operation is canceled and the database returns to the same state as it was before the operation began. At the same time, the client receives an error message that it is impossible to perform the operation due to the presence of child records.

    • You misunderstand me, now - up-and-coming
    • @ up-and-coming What am I getting you wrong? - Anton Shchyrov
    • But on a concrete example, if the references are in different tables for the same field. Where to write these restrictions, whether in both child tables. And does an error happen in case of contradictory restrictions on the field? - up-and-coming
    • @ up-and-coming Once again: the restriction is specified when creating each foreign key. What kind of restriction to specify depends on your business logic. If you did not specify a limit, then MySQL will substitute RESTRICT . When designing a database, you can specify any combinations. What happens when I update / delete I already wrote - Anton Shchyrov
    • I mean, there is a key reference in different AND NOT IN ONE TABLES. In one there is a link to the field from the third table and to the same field the third table is a link from the second table - up-and-coming