Circular or Cyclical References


Another Database Design Mistake To Avoid

One very bizarre design that I saw actually had circular references between tables.

The primary/foreign keys of two tables, were actually found in the other table.

Circular References in Foreign Keys

Note:  This modeling tool will not allow circular references.  The second line, I actually had to draw in manually.  Good tool!

Result:

I was asked to insert some rows for the java developers.  But it wasn’t possible.  You couldn’t insert into first table, because it needed to find the row (via the foreign key) in the second table.  But when you went to insert into the second table, you couldn’t there, because it needed to find a row in the first table.

I had to disable the constraints, insert into both tables, and then re-enable them.  It took a lot of time.

Disabling constraints is classically something a DBA would do, and only rarely.  It is not something a developer would do just to insert some data.

Correction:

There were many other problems with this “design”.  Circular references were just one of them.

Fortunately, the “designer” resigned soon after, and we were able to build a better design.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: