Flask-SQLAlchemy SQL Server Using strings as primary keys

Question

I'm building a webpage which uses SQL Server as the database + Flask for the web framework. I want to be able to add users to my webpage, and created a SQLAlchemy class as below:

from datetime import datetime
from app import db

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(), unique=True)
    password = db.Column(db.String())
    created_at = db.Column(db.DateTime(), default=datetime.now())

    def __repr__(self):
        return self.username

However, when I try to create the tables SQL Server gives me the following error:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Column 'email' in table 'user' is of a type that is invalid for use as a key column in an index. (1919) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint or index. See previous erro
    rs. (1750)")
    [SQL:
    CREATE TABLE [user] (
            id INTEGER NOT NULL IDENTITY(1,1),
            email VARCHAR(max) NULL,
            password VARCHAR(max) NULL,
            created_at DATETIME NULL,
            PRIMARY KEY (id),
            UNIQUE (email)
    )]

If I remove the Unique=True it works fine. I guess it has something to do with the SQL Server not understanding whats going on in the class or vice-versa.

Any ideas how to make a key Unique in SQL Server from SQLAlchemy?

score 4 · Answer 1 · answered Nov 08 '19 at 07:01

From SQLAlchemy docs:

SQL Server supports the special string “MAX” within the sqltypes.VARCHAR and sqltypes.NVARCHAR datatypes, to indicate “maximum length possible”. The dialect currently handles this as a length of “None” in the base type...

And the api reference for sqlalchemy.dialects.mssql.VARCHAR shows the constructor signature as:

class sqlalchemy.dialects.mssql.VARCHAR(length=None, collation=None, convert_unicode=False, unicode_error=None, _warn_on_bytestring=False, _expect_unicode=False)

As the default value of length is None and SQLAlchemy uses length=None to signal a max length varchar, all of your String columns are being defined as such, which you can see in the error message that you've posted:

..email VARCHAR(max) NULL, password VARCHAR(max) NULL

From SQL Server docs:

Columns that are of the ntext, text, image, varchar(max), nvarchar(max), and varbinary(max) data types cannot be specified as index key columns...

It goes on to say that they can be used as nonkey index columns in a clustered index, but according to this answer, varchar(max) will store over 2 billion single byte characters. I highly suspect that you don't need to store that many characters in an email field, so I'd suggest that limiting the length of the email column is the way to go here:

email = db.Column(db.String(256), unique=True)

Flask-SQLAlchemy SQL Server Using strings as primary keys

1 Answers1