'Unique constraints not detected in SQLAlchemy reflection
I'm attempting to use SQLAlchemy to detect constraints on an existing SQL database by reflection. First, I create some (what I think are) equivalent tables with slightly different syntax:
DROP TABLE IF EXISTS a;
DROP TABLE IF EXISTS b;
DROP TABLE IF EXISTS c;
CREATE TABLE IF NOT EXISTS a (
Id integer PRIMARY KEY,
Code text UNIQUE
);
CREATE TABLE IF NOT EXISTS b (
Id integer PRIMARY KEY,
[Code] text UNIQUE
);
CREATE TABLE IF NOT EXISTS c (
Id integer PRIMARY KEY,
Code text
);
GO
CREATE UNIQUE INDEX index_1 ON c (Code);
Then I try to reflect the table properties back through SQLAlchemy:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example.db'
db = SQLAlchemy(app)
engine = db.get_engine()
db.metadata.reflect(engine)
for table, table_data in db.metadata.tables.items():
print(table)
for c in table_data.constraints:
print(" ", c)
And get back:
a
UniqueConstraint(Column('Code', TEXT(), table=<a>))
PrimaryKeyConstraint(Column('Id', INTEGER(), table=<a>, primary_key=True))
b
PrimaryKeyConstraint(Column('Id', INTEGER(), table=<b>, primary_key=True))
c
PrimaryKeyConstraint(Column('Id', INTEGER(), table=<c>, primary_key=True))
Why is the unique constraint only detected for table a?
If I inspect the table outside of SQLAlchemy (using Database.NET), the generated DDL for each of the three tables looks like the format of table c (UNIQUE INDEX outside of CREATE TABLE statement). This led me to believe that any of these three methods were equivalent.
However, SQLAlchemy's behavior is now telling me that they are different, and furthermore it cannot detect any UNIQUE constraints unless defined in the first way. What am I missing here?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
