Issue: Constraints not generated when using `DeferredRelation` with `ForeignKeyField`
Author: frnhr
State: closed
Created: 2016-03-27 05:36:44

When using `DeferredRelation` with a `ForeignKeyField`, resulting MySQL table is missing a foreign key constraint. This constraint is present when regular model is passed to `ForeignKeyField`.

Complete code to replicate the issue...

foo.py:

```
""" Three models:

    PostNotDeferred ---< User >--- PostThatsDeferred
"""
from peewee import *

database = MySQLDatabase('peeweetest', host='localhost', user='peeweetest', password='peeweetest')

class BaseModel(Model):
    class Meta:
        database = database

class PostNotDeferred(BaseModel):
    title = CharField()

DeferredPost = DeferredRelation()

class User(BaseModel):
    username = CharField()
    post_not_deferred = ForeignKeyField(PostNotDeferred, null=True)
    post_thats_deferred = ForeignKeyField(DeferredPost, null=True)

class PostNotDeferred(BaseModel):
    title = CharField()

DeferredPost.set_model(PostThatsDeferred)

database.connect()
database.create_tables([User, PostNotDeferred, PostThatsDeferred])
```

Relevant part of mysqldump:

```
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `post_not_deferred_id` int(11) DEFAULT NULL,
  `post_thats_deferred_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_post_thats_deferred_id` (`post_thats_deferred_id`),
  KEY `user_post_not_deferred_id` (`post_not_deferred_id`),
  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`post_not_deferred_id`) REFERENCES `postnotdeferred` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

Only one `CONSTRAINT` is present, but two are expected.

complete `mysqldump -u peeweetest -p peeweetest` output:

```
-- MySQL dump 10.13  Distrib 5.6.23, for osx10.10 (x86_64)
--
-- Host: localhost    Database: peeweetest
-- ------------------------------------------------------
-- Server version   5.6.23

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `postnotdeferred`
--

DROP TABLE IF EXISTS `postnotdeferred`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `postnotdeferred` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `postnotdeferred`
--

LOCK TABLES `postnotdeferred` WRITE;
/*!40000 ALTER TABLE `postnotdeferred` DISABLE KEYS */;
/*!40000 ALTER TABLE `postnotdeferred` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `postthatsdeferred`
--

DROP TABLE IF EXISTS `postthatsdeferred`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `postthatsdeferred` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `postthatsdeferred`
--

LOCK TABLES `postthatsdeferred` WRITE;
/*!40000 ALTER TABLE `postthatsdeferred` DISABLE KEYS */;
/*!40000 ALTER TABLE `postthatsdeferred` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `post_not_deferred_id` int(11) DEFAULT NULL,
  `post_thats_deferred_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_post_thats_deferred_id` (`post_thats_deferred_id`),
  KEY `user_post_not_deferred_id` (`post_not_deferred_id`),
  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`post_not_deferred_id`) REFERENCES `postnotdeferred` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user`
--

LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2016-03-27  7:17:29
```


http://docs.peewee-orm.com/en/latest/peewee/models.html#circular-foreign-key-dependencies

If you read through that section, you'll see towards the end that you need to explicitly create the foreign key constraint:

> After initializing the deferred relation, the foreign key fields are now correctly set up. 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.
> 
> Here is how to create the tables:

``` python
# Foreign key constraint from User -> Tweet will NOT be created because the
# Tweet table does not exist yet. `favorite_tweet` will just be a regular
# integer field:
User.create_table()

# Foreign key constraint from Tweet -> User will be created normally.
Tweet.create_table()

# Now that both tables exist, we can create the foreign key from User -> Tweet:
# NOTE: this will not work in SQLite!
db.create_foreign_key(User, User.favorite_tweet)
```

---

In your example, for one I think you copy/pasted wrong and the model below `User` should be "PostThatsDeferred", but more to the point: there's no reason for using a deferred relation. You can just write:

``` python
class PostNotDeferred(BaseModel):
    title = CharField()

class PostThatsDeferred(BaseModel):
    title = CharField()

class User(BaseModel):
    username = CharField()
    post_not_deferred = ForeignKeyField(PostNotDeferred, null=True)
    post_thats_deferred = ForeignKeyField(PostThatsDeferred, null=True)
```


Thanks for your reply! It solved my problem.

I wasn't reading carefully that particular section of the docs because I don't have a circular dependency in the db. Instead, one module is imported late and that's why I'm using `DeferredRelation`.