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.

class User(Model):
    username = CharField()
    favorite_tweet = ForeignKeyField(Tweet, null=True)  # NameError!!
class Tweet(Model):
    message = TextField()
    user = ForeignKeyField(User, backref='tweets')

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

class User(Model):
    username = CharField()
    favorite_tweet_id = IntegerField(null=True)

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

class User(Model):
    username = CharField()
    # Tweet has not been defined yet so use the deferred reference.
    favorite_tweet = DeferredForeignKey('Tweet', null=True)
class Tweet(Model):
    message = TextField()
    user = ForeignKeyField(User, backref='tweets')
# Now that Tweet is defined, "favorite_tweet" has been converted into
# a ForeignKeyField.
print(User.favorite_tweet)
# <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:

# Will create the User and Tweet tables, but does *not* create a
# foreign-key constraint on User.favorite_tweet.
db.create_tables([User, Tweet])
# Create the foreign-key constraint:
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.