Circular foreign key dependencies

Sometimes it happens that you will create a circular dependency between two tables.

Note

My personal opinion is that circular foreign keys are a code smell and should be refactored (by adding an intermediary table, for instance).

Adding circular foreign keys with peewee is a bit tricky because at the time you are defining either foreign key, the model it points to will not have been defined yet, causing a NameError.

  1. class User(Model):
  2. username = CharField()
  3. favorite_tweet = ForeignKeyField(Tweet, null=True) # NameError!!
  4. class Tweet(Model):
  5. message = TextField()
  6. user = ForeignKeyField(User, backref='tweets')

One option is to simply use an IntegerField to store the raw ID:

  1. class User(Model):
  2. username = CharField()
  3. favorite_tweet_id = IntegerField(null=True)

By using DeferredForeignKey we can get around the problem and still use a foreign key field:

  1. class User(Model):
  2. username = CharField()
  3. # Tweet has not been defined yet so use the deferred reference.
  4. favorite_tweet = DeferredForeignKey('Tweet', null=True)
  5. class Tweet(Model):
  6. message = TextField()
  7. user = ForeignKeyField(User, backref='tweets')
  8. # Now that Tweet is defined, "favorite_tweet" has been converted into
  9. # a ForeignKeyField.
  10. print(User.favorite_tweet)
  11. # <ForeignKeyField: "user"."favorite_tweet">

There is one more quirk to watch out for, though. When you call create_table we will again encounter the same issue. For this reason peewee will not automatically create a foreign key constraint for any deferred foreign keys.

To create the tables and the foreign-key constraint, you can use the SchemaManager.create_foreign_key() method to create the constraint after creating the tables:

  1. # Will create the User and Tweet tables, but does *not* create a
  2. # foreign-key constraint on User.favorite_tweet.
  3. db.create_tables([User, Tweet])
  4. # Create the foreign-key constraint:
  5. User._schema.create_foreign_key(User.favorite_tweet)

Note

Because SQLite has limited support for altering tables, foreign-key constraints cannot be added to a table after it has been created.