1

To me, the UNIQUE keyword is for better structuring your table (putting aside the PRIMARY KEY, which is for identification purposes). For example, files. If you don't want to add the same file to the database you could use its SHA-2 hash key as a UNIQUE KEY. But SHA-2 is 512 characters, and InnoDB has a limit of 255. Why does MySQL have this limit and how does one use larger, more reliable, unique keys such as SHA-2

puk
  • 16,318
  • 29
  • 119
  • 199

2 Answers2

3

SHA2-512 gives 512 bits which is 64 bytes. Or char(128) in hex.

The unique index key length is 1000 bytes for MyISAM and 767 for InnoDB in MySQL 5.5.

Unique is used to enforce natural uniqueness when you choose to have an AUTO_INCREMENT surrogate primary key. Or superkey uniqueness when you want to enforce subtypes. etc.

Edit:

To enforce uniqueness over LOB values, you just hash it and add a constraint. If you have enough rows to have a measurable risk of collisions, then why do you have so many LOB values in the database?

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
1

In MySQL UNIQUE is for indexing. Better not to use it for checkign uniqueness of text fields.

I always check data integrity and uniqueness in application, not MySQL.

Oroboros102
  • 2,214
  • 1
  • 27
  • 41
  • 1
    You have a corrupt database. If not now, at some point. -1 for thinking you can check uniqueness over, say, 100 million rows in your application better then the DB engine – gbn Nov 14 '11 at 09:47
  • @gbn I'm confused now. Are you saying that `UNIQUE` should be used for structuring the table? What if I want my users table to enforce that everyone has a unique ID (automatic), e-mail, username and nickname, all of max length larger than 255. Should I check in application, as OP suggests, or let MySQL deal with it. – puk Nov 14 '11 at 10:18
  • 1
    Both: MySQL enforces unique on a hash generated by the app. In other RDBMS you'd use a computed column but MySQL doesn't support them – gbn Nov 14 '11 at 10:25
  • @gbn "MySQL enforces unique on a hash generated by the app"-> could you...simplify? That went way over my head. – puk Nov 14 '11 at 10:28
  • Your php generates a hash over e-mail, username and nickname. The uniquesness of the hash is enforced by unique index on a hash column in mysql. – gbn Nov 14 '11 at 10:44
  • @gbn therefore, when something gets too large (ie. `varchar(65535)`) just hash it, and use that unique index instead – puk Nov 14 '11 at 10:50
  • Yes, I would. Then you can leverage the DB engine ability to enforce unique over millions of rows that your app can't – gbn Nov 14 '11 at 10:55
  • Puk, Gbn, is right. My answer is wrong. Unique index over hash is much better and proper way. – Oroboros102 Nov 14 '11 at 13:30