MySQL TEXT or BLOB type cannot be unique?

Question

I have a table named users and here is the schema of it:

class User(db.Model):
__tablename__ = "Users"
id = db.Column(db.Integer, unique=True, primary_key=True)
firstname = db.Column(db.String(64), unique=False, nullable=False)
lastname = db.Column(db.String(64), unique=False, nullable=False)
email = db.Column(db.String(120), unique=True, nullable=False)
password = db.Column(db.String(93), unique=False, nullable=False)
phone = db.Column(db.String(10), unique=True, nullable=False)
publickey = db.Column(db.Text, unique=True, nullable=True)
isitseller = db.Column(db.Boolean, unique=False, nullable=False)
money = db.Column(db.Float, unique=False, nullable=False)

def __init__(self, firstname, lastname, email, password, phone, publickey, isitseller):
    self.firstname = firstname
    self.lastname = lastname
    self.email = email
    self.password = generate_password_hash(password)
    self.phone = phone
    self.publickey = publickey
    self.isitseller = isitseller
    self.money = 0.00

def check_password(self, password):
    return check_password_hash(self.password, password)

def __repr__(self):
    return '<User %r>' % self.firstname + " " + self.lastname

But when I try to create users table with db.create_all() it outputs me

(_mysql_exceptions.OperationalError) (1170, "BLOB/TEXT column 'publickey' used in key specification without a key length") [SQL: '
CREATE TABLE `Users` (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    firstname VARCHAR(64) NOT NULL, 
    lastname VARCHAR(64) NOT NULL, 
    email VARCHAR(120) NOT NULL, 
    password VARCHAR(93) NOT NULL, 
    phone VARCHAR(10) NOT NULL, 
    publickey TEXT, 
    isitseller BOOL NOT NULL, 
    money FLOAT NOT NULL, 
    PRIMARY KEY (id), 
    UNIQUE (id), 
    UNIQUE (email), 
    UNIQUE (phone), 
    UNIQUE (publickey), 
    CHECK (isitseller IN (0, 1))
)

']

As I understand I would need to use anything out of TEXT or BLOB type of colunm but how can I do that? or any way to using TEXT or BLOB as a UNIQUE?

`publickey`must be unique and can be `null`? ;) Despite this, the real error can be found on https://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length — GuyT, Oct 21 '17 at 13:43

score 3 · Accepted Answer · answered Oct 21 '17 at 13:48

You are confusing "key" with "primary key". In MySQL, a key just refers to an index in this context.

I would strongly recommend that you use varchar() for the field, rather than text -- unless it really can be really big.

In any case, MySQL has a limit on the size of an index key. And, text is unlimited in size. Contradiction. This is resolved -- as the documentation explains -- by requiring a length:

For indexes on BLOB and TEXT columns, you must specify an index prefix length. For CHAR and VARCHAR, a prefix length is optional. See Section 8.3.4, “Column Indexes”.

Unfortunately, this also means that you cannot declare a text column as unique, because that requires an index.

I don't think unique in a create table statement accepts lengths. You can define a unique index on a prefix:

create unique index unq_users_ publickey on users(publickey(100));

Be careful about the maximum length. MySQL puts a limit based on bytes, which means that the character coding affects the maximum number of characters (100 is safe).

MySQL TEXT or BLOB type cannot be unique?

1 Answers1