[Sqlalchemy-tickets] Issue #3019: id column with serial small integer primary key (zzzeek/sqlalchemy)

[Moritz B. <iss...@bi...>]

New issue 3019: id column with serial small integer primary key
https://bitbucket.org/zzzeek/sqlalchemy/issue/3019/id-column-with-serial-small-integer

Moritz Beber:

It's rather common to declare an `id` column as primary key which is usually converted effortlessly. (I work with Flask-SQLAlchemy and a postgresql database.)

```python
class Dummy(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(16), unique=True, index=True)
```

Emits the following statement:

```
CREATE TABLE dummy (
        id SERIAL NOT NULL, 
        name VARCHAR(16), 
        PRIMARY KEY (id)
)
```

Perfect! However, when I declare the id column with a small integer:

```python
class Dummy(db.Model):
    id = db.Column(db.SmallInteger, primary_key=True)
    name = db.Column(db.String(16), unique=True, index=True)
```

The emitted statement is:

```
CREATE TABLE dummy (
        id SMALLINT NOT NULL, 
        name VARCHAR(16), 
        PRIMARY KEY (id)
)
```
and adding an object naturally leads to an error:

```
(IntegrityError) null value in column "id" violates not-null constraint
 'INSERT INTO dummy (name) VALUES (%(name)s) RETURNING dummy.id' {'name': 'crash test'}
```

Is there a more profound reason than a type check preventing this from being possible?