Issue: constraints of ForeignKeyField cause syntax error
Author: elonzh
State: closed
Created: 2018-05-02 09:36:01

I have some models based on the database which was created by sql.
here is ddl for the model `Book`:
```sql
create table fennec.user_books
(
	id int unsigned auto_increment
		primary key,
	book_id int unsigned not null,
	user_type smallint(5) unsigned not null,
	user_id int not null,
	stage smallint(5) unsigned not null,
	created_at timestamp(6) default CURRENT_TIMESTAMP(6) not null,
	updated_at timestamp(6) default CURRENT_TIMESTAMP(6) not null,
	constraint user_books_learning_unique
		unique (book_id, user_type, user_id),
	constraint user_books_book_id_foreign
		foreign key (book_id) references fennec.books (id)
			on delete cascade
)
engine=InnoDB
;

create index user_books_book_id_index
	on fennec.user_books (book_id)
;
``` 

and the model `Book`: 

```python
class Book(pwx.Model, SerializeMixin, SequenceMixin, ResourceMixin):
    __seq_scope_field_name__ = 'series'
    __appends__ = ['cover_urls', 'video_urls']

    class Meta:
        per_page = 3
        table_name = 'books'

    id = pw.AutoField()
    series = pw.ForeignKeyField(Series, column_name='series_id', backref='books', index=True)

    sequence = pw.DoubleField()

    title = pw.CharField(max_length=45)
    chinese_title = pw.CharField(max_length=45, null=True)

    status = pw.SmallIntegerField(
        choices=enum2choices(ResourceStatus),
        default=ResourceStatus.UNPUBLISHED.value
    )
    description = pw.CharField(max_length=512, null=True)
    cover_name = pw.CharField(max_length=255)
    video_name = pw.CharField(max_length=255)
    lyric = pw.TextField()

    @validates(InclusionValidator(*enum_values(ResourceStatus)))
    def validate_status(self, value):
        return value

    @property
    def cover_urls(self):
        return media.get_media_urls(current_app.config['MEDIA_PRI_IMG_BUCKET'], self.cover_name, private=True)

    @property
    def video_urls(self):
        return media.get_media_urls(current_app.config['MEDIA_PRI_VID_BUCKET'], self.video_name, private=True)

    @staticmethod
    def of_series(series):
        return Book.select().where(Book.series == series)
```

now we need a new model `TrialBook`:

```python
class TrialBook(pwx.Model, SerializeMixin):
    class Meta:
        table_name = 'trial_books'

    id = pw.AutoField(constraints=[pw.SQL("UNSIGNED")])
    book = pw.ForeignKeyField(Book, column_name='book_id', index=True)
```

we added `UNSIGNED` for `TrialBook.id` because the ddl of `Book.id` is `UNSIGNED`.

but when we got a syntax error like that:
```
ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNSIGNED, FOREIGN KEY (`book_id`) REFERENCES `books` (`id`))' at line 1")
```

ddl for `TrialBook`:
```
('CREATE TABLE IF NOT EXISTS `trial_books` (`id` INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, `book_id` INTEGER NOT NULL unsigned, FOREIGN KEY (`book_id`) REFERENCES `books` (`id`))',
 [])
```



peewee version: 3.2.2
database: mysql 5.6

i changed the sql to
```sql
CREATE TABLE IF NOT EXISTS `trial_books` (`id` INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, `book_id` INTEGER UNSIGNED NOT NULL, FOREIGN KEY (`
                             book_id`) REFERENCES `books` (`id`))
``

it works.

that is, ``book_id` INTEGER NOT NULL UNSIGNED` is a wrong sql for mysql




[13.1.17 CREATE TABLE Syntax](https://dev.mysql.com/doc/refman/5.6/en/create-table.html)

```
column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]

data_type:
    BIT[(length)]
  | TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | DATE
  | TIME[(fsp)]
  | TIMESTAMP[(fsp)]
  | DATETIME[(fsp)]
  | YEAR
  | CHAR[(length)]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | VARCHAR(length)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | BINARY[(length)]
  | VARBINARY(length)
  | TINYBLOB
  | BLOB[(length)]
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | TEXT[(length)]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | MEDIUMTEXT
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | LONGTEXT
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | ENUM(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | SET(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | spatial_type
```

You're probably best off creating your own field type.

```python
class UnsignedIntegerField(IntegerField):
    field_type = 'INTEGER UNSIGNED'
```