I have such a problem, I hope for understanding and help. A database is created in the query, followed by the creation of tables (15 pcs.). There are such tables that in the 1st table there is a foreign key to the 2nd table, but also in the 2nd table there is a foreign key on the 1st table. How to arrange them so that there is no error that the table 1 cannot be created during compilation because there is a foreign key column for a table that is not created 2 . There is an idea to create a third table, but how to create it correctly then what should be present in it?

    4 answers 4

    First create the table without foreign keys, and then the keys separately. For example:

    CREATE TABLE `T1` (...) CREATE TABLE `T2` (...) ... CREATE TABLE `T15` (...) ALTER TABLE `T1` ADD CONSTRAINT `C1` FOREIGN KEY (`F1`) REFERENCES `T2`(`F2`) ...
    • Hmm ... you can try, ATP. And after 3 tables you can tell? - Misha_xD
    • Honestly, I hardly imagine such a realization. I would review the architecture, because it can be dangerous to loop tables in this way. Maybe someone else will offer something ... - _perchuk
    • Explain about looping? those. But about this situation, which I described? In the conceptual and relational models, this is how it goes. Otherwise, do not implement. - Misha_xD

    Incorrect database architecture. 2 tables should not be mutually referenced to each other. Tell us why you need such links? PS in foreign keys.

    • Service Center DB, staff table and client table. After the diagnostics, the employee (table staff) must notify the client (client table) that the cost of diagnosis has been performed as such, in response to this, the client must inform the employee whether or not the repair agrees. Link implemented in 2 directions for this and there are foreign keys on each other. And I do not see another solution how to connect these 2 tables. That's why I use foreign keys for each other. - Misha_xD
    • Employee table. Customer table The technology acceptance table or something like that, referring to the "Employee table and Customer table". If you need a client-employee dialogue, then another table of comments, etc., whose entries refer to the adoption tables (the preferred option). Otherwise, in the adoption table, we write a malfunction in one field according to the customer, to another, diagnostic results - Lapitop Putiat'evich Nyashkin
    • Dilbert: Oh no! The ultimate level of abstraction made us weightless! - Lapitopa Putievich Nyashkin
    • That's exactly what I want to realize the third table, to which both the customer table and the employee table will refer, I asked how I should implement it correctly, what should be in it and what should not. - Misha_xD
    • I do not know the specifics of your SC. If it concerns only diagnostics, then it is abstract: “Technique reception” table. Fields: -Identifier -Client (identifier) ​​-Accepted employee (identifier) ​​-Diagnosed employee (identifier) ​​-Cause (problems with the words of the client) -Diagnosis result -Cost, etc. - payment flag - amount of payment - Further work with the client. (i.e., after diagnostics, repair, replacement of components, etc., followed. which should be reflected already in another subsystem of your software) - Lapitop Putiatyevich Nyashkin

    Employee table. Customer table The technology acceptance table or something like that, referring to the "Employee table and Customer table". If you need a client-employee dialogue, then another table of comments, etc., whose entries refer to the adoption tables (the preferred option). Otherwise, in the adoption table, we write a fault in one field from the words of the client, to another, the diagnostic results.

      Transaction ... On pseudocode something like this:

       Создать Запись в Таблице1 ( ID = Автоинкремент(), КлючТаблица2 = Null ); @key = ПоследнийАвтоинкремент(); Создать Запись в Таблице2 ( ID = Автоинкремент(), КлючТаблица1 = @key ); @key 2 = ПоследнийАвтоинкремент(); Изменить Запись в Таблице1 где ID = @key задать КлючТаблица2 = @key 2;