CODEWITHSUNDEEP
sqlitesqlalchemy
user784637
user784637

Reputation: 16152

How to create non-unique index on column in sqlalchemy?

I'm trying to create a non-unique index on the file_name column. I'm expecting a high cardinality, say for 1,000 rows, there are 950 unique file names.

file_collection = Table ('file_collection', metadata,
    Column('id', Integer, primary_key=True),
    Column('full_path', String, unique=True, nullable=False),
    Column('file_name', String, index=True, nullable=False)
)

My dialect is sqlite. When I create the table, a non-unique key is not created on the file_name column

CREATE TABLE file_collection (
        id INTEGER NOT NULL,
        full_path VARCHAR NOT NULL,
        file_name VARCHAR NOT NULL,
        PRIMARY KEY (id),
        UNIQUE (full_path)
)

How can I create a non-unique key on the file_name column?

Upvotes: 4

Views: 3060

Answers (1)

Mark Hildreth
Mark Hildreth

Reputation: 43111

Remember that SQLite requires you to create a non-unique index as a separate statement rather than in the actual CREATE TABLE statement like some RDBMs allow. Assuming I have the following table:

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), index=True)

The creation will run as follows.

2014-01-03 17:28:48,229 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("users")
2014-01-03 17:28:48,229 INFO sqlalchemy.engine.base.Engine ()
2014-01-03 17:28:48,229 INFO sqlalchemy.engine.base.Engine
CREATE TABLE users (
        id INTEGER NOT NULL,
        email VARCHAR(255),
        PRIMARY KEY (id)
)


2014-01-03 17:28:48,230 INFO sqlalchemy.engine.base.Engine ()
2014-01-03 17:28:48,230 INFO sqlalchemy.engine.base.Engine COMMIT
2014-01-03 17:28:48,230 INFO sqlalchemy.engine.base.Engine CREATE INDEX ix_users_email ON users (email)
2014-01-03 17:28:48,230 INFO sqlalchemy.engine.base.Engine ()
2014-01-03 17:28:48,231 INFO sqlalchemy.engine.base.Engine COMMIT

Note that the CREATE TABLE statement does not show the index being created, but it IS being created in a follow-up statement slightly after.

Upvotes: 5

Related Questions