CREATE TABLE fails by default in MS SQL Server due to Index = True being the default #38
Description
First Check
- I added a very descriptive title to this issue.
- I used the GitHub search to find a similar issue and didn't find it.
- I searched the SQLModel documentation, with the integrated search.
- I already searched in Google "How to X in SQLModel" and didn't find any information.
- I already read and followed all the tutorial in the docs and didn't find an answer.
- I already checked if it is not related to SQLModel but to Pydantic.
- I already checked if it is not related to SQLModel but to SQLAlchemy.
Commit to Help
- I commit to help with one of those options 👆
Example Code
from typing import Optional
from sqlmodel import Field, SQLModel, create_engine
# DATABASE CONFIG
DB_DRIVER = "ODBC Driver 17 for SQL Server"
DB_HOST = "some_sql_server"
DB_DATABASE = "a_database"
engine = create_engine(
f"mssql+pyodbc://@{DB_HOST}/{DB_DATABASE}?&driver={DB_DRIVER}"
)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
secret_name: str
age: Optional[int] = None
SQLModel.metadata.create_all(engine)Description
- Create a Hero model
- Attempt to
create_all(engine), where the engine is configured against SQL Server.
(pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'secret_name' in table 'hero' is of a type that is invalid for use as a key column in an index. (1919) (SQLExecDirectW)")
[SQL: CREATE INDEX ix_hero_secret_name ON hero (secret_name)]
(Background on this error at: https://sqlalche.me/e/14/f405)
I believe this is due to the column being VARCHAR(MAX) which MS SQL Server does not see as a column that can be used for indexing.
Operating System
Windows
Operating System Details
No response
SQLModel Version
0.0.4
Python Version
Python 3.9.6
Additional Context
Happy to write up a PR, but I think the default for indexing either needs to be switched to False by default, or implement some form of dialect check.
P.S. I don't contribue much on GitHub, so please let me know if there's any further context/assistance I can provde. 😄