CODEWITHSUNDEEP
mysqlscaladatabase-schemaslick-3.0
surenyonjan
surenyonjan

Reputation: 2109

Slick 3.1.1 not creating unique constraint

I have simple User table with id set as auto increment and also username to be set as unique in table.

class Users (tag: Tag) extends Table[User](tag, "Users") {

  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def username = column[String]("username", NotNull)
  def email = column[String]("email")
  def passwordHash = column[String]("password_hash")
  def createdAt = column[Long]("created_at")
  def updatedAt = column[Long]("updated_at")
  def lastLoggedInAt = column[Long]("last_logged_in_at")

  override def * = (id.?, username, email, passwordHash, createdAt, updatedAt, lastLoggedInAt) <> (User.tupled, User.unapply)

  def idxUsername = index("idx_username", username, unique = true)
}

But when creating table using slick (db.run(users.schema.create)) does not create unique index idx_username on database

Here is the create statements of schema users.schema.createStatements.foreach(println):

create table `Users` (`id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,`username` TEXT NOT NULL,`email` TEXT NOT NULL,`password_hash` TEXT NOT NULL,`created_at` BIGINT NOT NULL,`updated_at` BIGINT NOT NULL,`last_logged_in_at` BIGINT NOT NULL)
create unique index `idx_username` on `Users` (`username`,`email`)

Database query for index on Users Table

mysql> SHOW INDEX FROM Users;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

Am I missing something to create unique constraint when executing DDL function?

Upvotes: 3

Views: 1879

Answers (1)

Johny T Koshy
Johny T Koshy

Reputation: 3922

TEXT column cannot be used as UNIQUE. If you try,

db.run(users.schema.create).onFailure{
  case x => println("error " + x)
}

it will print,

error com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: BLOB/TEXT column 'username' used in key specification without a key length

As a work around you could specify the type and length,

def username = column[String]("username", O.SqlType("VARCHAR(65535)"))

Upvotes: 6

Related Questions