2

So I am a bit lost. I cannot create my schema because I am looped in a FK cycle. Each table needs the next to be created.

  • Company has FK to a city
  • City has FK to a country
  • Country has FK to a currency
  • Currency has FK to a company (central bank that issues the currencies.)

So how do I go about creating these tables? These are all required FKs.

Gustavo Mori
  • 8,319
  • 3
  • 38
  • 52
Matty
  • 21
  • 1
  • [Possible duplicate.](http://stackoverflow.com/questions/955671/dealing-with-circular-reference-when-entering-data-in-sql) – Peter K. Jun 01 '11 at 01:18
  • That link talks about disabling FK or using a dummy one to get the data in. But I want to see without hacking the schema how to make the schema work by changing relationships around. – Matty Jun 01 '11 at 01:30
  • You need to set one of them as nullable (remove the NOT NULL constraint). – Peter K. Jun 01 '11 at 01:32

2 Answers2

1

You need to create relational tables where the elements are FKs, to break the circular reference:

Introduce CompanyCurrency table, where the PK is either a compounded key (FK Company + FK Currency), if it is unique, or an ordinal autogenerated value (probably a better choice)

enter image description here

If you really wanted to improve your design, you would keep on doing this for all tables. So that you would only have (PK, Name) for all tables, and then have relational tables for all those that need it.

Gustavo Mori
  • 8,319
  • 3
  • 38
  • 52
0

Create all the tables with primary keys, and then add all the foreign keys afterwards.

WW.
  • 23,793
  • 13
  • 94
  • 121