Unique constraint on VARCHAR #154

Closed
andrewthad opened this Issue Sep 14, 2013 · 3 comments

Comments

Projects
None yet
4 participants
Contributor

andrewthad commented Sep 14, 2013

My table is as follows:

User
    plugin Text maxlen=200
    identifier Text maxlen=200
    fullName Text
    role Role
    UniqueUser plugin identifier

This works fine (I am using MySQL), but if I try maxlen=50 or anything less than 200, it breaks. The unique constraint expects the length to be at least 200. I think this is because of the problems with indexing a TEXT field. In the case of using TEXT instead of VARCHAR, indexing on a prefix of length 200 sort of makes sense (as much as indexing on TEXT can make sense). However, with a VARCHAR whose max length is known, there should not be a prefix at all. Basically, I think it makes more sense to have this:

"ALTER TABLE `user` ADD CONSTRAINT `unique_user`
UNIQUE(`plugin`,`identifier`)"

than this:

"ALTER TABLE `user` ADD CONSTRAINT `unique_user`
UNIQUE(`plugin`(200),`identifier`(200))"

@gregwebs gregwebs added the MySQL label Aug 4, 2014

I've had the same issue today. The 200 value seems to be hardcoded:

escapeDBName' (name, (FTTypeCon _ "Text" )) = escapeDBName name ++ "(200)"
escapeDBName' (name, (FTTypeCon _ "String" )) = escapeDBName name ++ "(200)"
escapeDBName' (name, (FTTypeCon _ "ByteString")) = escapeDBName name ++ "(200)"

It would be interesting to know if this is the expected behavior.

Member

MaxGabriel commented May 25, 2015

This may be closable? It looks like specifying maxlen causes a VARCHAR column to be created instead of TEXT. If that's the intended behavior, and I think it probably is because I don't think a maximum length can be applied to a TEXT column in MySQL, then it seems like maxlen is working.

Using a similar models file to what @andrewthad posted:

Example
    plugin Text maxlen=200
    identifier Text maxlen=50
    fullName Text
    UniqueExample plugin identifier

I get the following migrations:

Migrating: CREATe TABLE `example`(`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,`plugin` VARCHAR(200) CHARACTER SET utf8 NOT NULL,`identifier` VARCHAR(50) CHARACTER SET utf8 NOT NULL,`full_name` TEXT CHARACTER SET utf8 NOT NULL)
24/May/2015:20:58:11 -0700 [Debug#SQL] CREATe TABLE `example`(`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,`plugin` VARCHAR(200) CHARACTER SET utf8 NOT NULL,`identifier` VARCHAR(50) CHARACTER SET utf8 NOT NULL,`full_name` TEXT CHARACTER SET utf8 NOT NULL); [] @(<unknown>:<unknown> <unknown>:0:0)
Migrating: ALTER TABLE `example` ADD CONSTRAINT `unique_example` UNIQUE(`plugin`(200),`identifier`(50))
Devel application launched: http://localhost:3000
24/May/2015:20:58:11 -0700 [Debug#SQL] ALTER TABLE `example` ADD CONSTRAINT `unique_example` UNIQUE(`plugin`(200),`identifier`(50)); [] @(<unknown>:<unknown> <unknown>:0:0)

Yielding the following mysql schema:

mysql> SHOW CREATE TABLE example\G
*************************** 1. row ***************************
       Table: example
Create Table: CREATE TABLE `example` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `plugin` varchar(200) NOT NULL,
  `identifier` varchar(50) NOT NULL,
  `full_name` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_example` (`plugin`,`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Member

MaxGabriel commented Jun 6, 2015

(Closing this based off the previous comment)

@MaxGabriel MaxGabriel closed this Jun 6, 2015

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment