constraints of ForeignKeyField cause syntax error

[elonzh]

I have some models based on the database which was created by sql. here is ddl for the model Book:

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:

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:

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`))',
 [])

[elonzh]

peewee version: 3.2.2 database: mysql 5.6

[elonzh]

i changed the sql to

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

[elonzh]

13.1.17 CREATE TABLE Syntax

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

[coleifer]

You're probably best off creating your own field type.

class UnsignedIntegerField(IntegerField):
    field_type = 'INTEGER UNSIGNED'