Is it possible to have circular foreign key dependencies in a relational database?

I am writing some code to back up a MySQL database to a binary. I know mysqldump

, but for some reason I cannot use trivial methods. What I am doing now:

• read schema definition
• sort tables by foreign key dependencies
• select rows in all tables (100 rows each time) and write them to binary

Dependency Definition: A table is T1

dependent on the existence of the table T2

if and only if there is at least one foreign key in T1

pointing to the key T2

.

Each column is assigned a numeric value. This value determines the order of the tables. For tables without dependencies, this value 0

for others is the maximum value of tables on which the current table depends; plus one. If there are dependent tables in the set of values -1

, the value of the current table remains undefined ( -1

). Initially the value of all tables -1

, which means unspecified.

This is the C ++ code:

// tablesQueue: Queue of all tables
// orderedQueue: Resulting order

while(! tablesQueue.isEmpty())
{
    bool satisfied = true;
    foreach(TableNode* parent, tablesQueue.head()->referencedTables)
    {
        if(parent->degreeOfFreedom == -1)
        {
            satisfied = false;
            break;
        }
        else
           // handle error blah blah ... 
    }
    if(satisfied)
    {
        int max =0;
        foreach(TableNode* parent, tablesQueue.head()->referencedTables)
        max = (max < parent->degreeOfFreedom+1) ? 
                  parent->degreeOfFreedom+1 : max;
        tablesQueue.head()->degreeOfFreedom = max;
        orderedQueue.enqueue(tablesQueue.dequeue());
    }
    else
    {
        tablesQueue.enqueue(tablesQueue.dequeue());
    }
}

      

        
        
        
      

    

If there is a loop in the table dependency graph, this algorithm does not end.

Is it usually OK to have this kind of design for tables? For example, two tables have a foreign key to each other. Surprisingly, I have found that sakila

there are many such loops in the database examples provided by Oracle for MySQL ( ). I guess it is possible to remove all of these loops by adding a third table [?]