About unique=True and (unique=True, index=True) in sqlalchemy

Question

When I create tables use flask-sqlalchemy like this:

class Te(Model):
    __tablename__ = 'tt'
    id = Column(db.Integer(), primary_key=True)
    t1 = Column(db.String(80), unique=True, )
    t3 = Column(db.String(80), unique=True, index=True, )

and In my Sequel Pro , I get the table create info:

CREATE TABLE `tt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t1` varchar(80) DEFAULT NULL,
  `t3` varchar(80) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `t1` (`t1`),
  UNIQUE KEY `ix_tt_t3` (`t3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

this means t1 is entirely same as t3 in MySQL? So when you define unique=True, it's not require to define index=True?

Thanks.

Answer 1

I think you have a term confusion with the index purpose in sqlalchemy. In sql databases index are used speed up query performance.

According to the sqlalchemy documentation of defining constraints and indexes.

You would notice the use of the index key because the sql code generated is:

UNIQUE KEY `ix_tt_t3` (`t3`)

The way how sqlalchemy nouns the index is idx_%columnlabbel. And that matches with the sql code generated.

So the use or not of index it is only related with performance and the unique key means that the column values cannot be repeated all along of the same column in the 'tt' table.

Hope this helps,

Answer 2

It is not required. A unique constraint is more often than not implemented using a unique index, but you need not care about that detail, if all you want is uniqueness.