All geek questions in one place

Two tables reference each other: how to insert a row into an Oracle database?

I have two tables

  • Department
  • Professor

in which the Department has a HeadID attribute referencing Professor and the Professor has a DeptID attribute referencing Department

They form a circular bond.

But the problem is how to insert a row into any of these tables?

Oracle complained that the "parent key was not found" after I tried to insert a row.

+4
source share
5 answers

You can define one of the foreign key constraints as DEFERRABLE and defer the check constraint until the end of the transaction (instead of checking at the end of the statement that ends with "parent key not found"). Read here

+11
source

The other solutions described here are simpler.
But if you really want the database to describe your business (which is not always the best approach), you can get another table, say DEPT_HEAD_POSITIONS . the Department table will have FK (HeadID) referring to this table, and the Professor table will have another field with a null value like FK for this new table.

Now you have:

  • sections of head offices
  • departments (which should have a head)
  • professors (who should belong to the department and may be the head of the department)
+1
source

For a foreign key consisting of several columns, it is possible that one of the columns contains a value for which the reference columns do not have a corresponding value, according to the SQL-92 standard. To avoid this situation, create NOT NULL constraints for all foreign key columns.

for reference

so I think you can insert data into one of the rows without specifying the value in the foreign key column, and then insert the row into the row value of the second primary key in the first table, and then you can continue ...

0
source

If you have the right to reverse engineer the circuit, you must. If not, I think the easiest and best approach is described in the deathApril comment.

In the event that you want to add a new department and a new professor who heads it, you are best:

  • Adding a professor to another department
  • Adding a department with a professor from step 1 as a chapter
  • Updating the professor’s record from step 1 to link to his new department created in step 2
0
source

Oracle and SQL Server do not allow circular references, because there is always a problem when deleting a row from a table that has dependencies to another row from another table (foreign key) that refers to the row to be deleted ..... For more information: Click here

-1
source

Source: https://habr.com/ru/post/1403306/

More articles:


All Articles