CODEWITHSUNDEEP
mysqlunique
puk
puk

Reputation: 16782

What's the purpose of UNIQUE in MySQL (InnoDB)

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

Upvotes: 1

Views: 1105

Answers (2)

gbn
gbn

Reputation: 432271

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?

Upvotes: 3

Oroboros102
Oroboros102

Reputation: 2254

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.

Upvotes: 1

Related Questions