Are circular references acceptable in database?

Question

When are circular references acceptable in database?

Theoretical and practical, any help is appreciated.

Answer 1

Consider cities and states. Each city exists within a state. Each state has a capital city.

CREATE TABLE city (   city  VARCHAR(32),   state VARCHAR(32) NOT NULL,   PRIMARY KEY (city),   FOREIGN KEY (state) REFERENCES state (state) );  CREATE TABLE state (   state VARCHAR(32),   capital_city VARCHAR(32),   PRIMARY KEY (state),   FOREIGN KEY (capital_city) REFERENCES city (city) ); 

First problem - You cannot create these tables as shown, because a foreign key can't reference a column in a table that doesn't (yet) exist. The solution is to create them without the foreign keys, and then add the foreign keys afterwards.

Second problem - you cannot insert rows into either table, as each insert will require a pre-existing row in the other table. The solution is to set one of the foreign key columns to be NULL, and insert that data in two phases. e.g.

--Create state record INSERT INTO state (state, capital_city) VALUES ('Florida', NULL);  --Create various city records INSERT INTO city (city, state) VALUES ('Miami', 'Florida'); INSERT INTO city (city, state) VALUES ('Tallahassee', 'Florida'); INSERT INTO city (city, state) VALUES ('Orlando', 'Florida');  --Set one of the cities as the capital UPDATE state SET capital_city = 'Tallahassee' WHERE state = 'Florida';