MySQL: How to make data unique for several rows in a table?

Question

How would I do the following (interested only in how to do UNIQUE (title, description)) in myphpadmin?:

CREATE TABLE `myData` (
    id serial primary key,
    title varchar(255) not null,
    description varchar(255) not null,
    UNIQUE (title, description)
);

And by the way, so as not to open another question on SO, I would like to know what other types I can use for long strings? Because 225 characters are too small for our purposes. Though I know that varchar can accept up to 65,535 characters as of MySQL 5.0.3. But could I specify those fields as MEDIUMTEXT (my client wants those to be as such for compatibility sake)? When I do so I get an error because "BLOB/TEXT column ... used in key specification without a key length". What should I do then? I need those two fields to be unique - so that I would be able to store similar titles even when descriptions are different, but when there are titles and descriptions that match titles and descriptions of incoming data, I wish not to store this incoming data then.

(I already opened a question on SO at http://stackoverflow.com/questions/2378784/mysql-problem-to-store-only-different-data - but because my cookie was turned off when I asked I can't now get to the question. Otherwise I would ask these questions along with that - and I'd love to thank Atli who answered my privious question. Man, you really helped me!)

Answer 1

Default maximum key length is 1000 bytes for MyISAM and 3072 bytes for InnoDB (with additional requirement that each column in the key should not exceed 767 bytes).

If your table is InnoDB or MyISAM with a single-byte encoding, then you can create the constraint alright.

If it's not, then you can create a prefixed index, but be aware that the index will only maintain uniqueness within the prefix.

It will be much better, however, that you hash your title and description and store the hash in a unique column instead.

Answer 2

To create the UNIQUE using PhpMyAdmin (i hope this was your question ;) ) do the following:

Navigate to the Table and select "structure", then add an index over 2 rows (bottom left), select the two fields and "indextyp: unique" and your done.

Edit: Added Screenshots, it's german but it should look the same to you. I really hope that does the trick, if not i didn't understand your question and all the comments you made at all.

http://dl.dropbox.com/u/3615626/stackoverflow/phpmyadmin-step1.png

http://dl.dropbox.com/u/3615626/stackoverflow/phpmyadmin-step2.png

it is filed under "index" because "unique" is just a special type of index.

Answer 3

CREATE TABLE `myData` (
`id` INT( 12 ) NOT NULL ,
`title` TEXT NOT NULL ,
`description` TEXT NOT NULL ,
PRIMARY KEY ( `id` ) ,
UNIQUE (
`title` (255) ,
`description` (255)
)
);

MySQL will store full information from title and description, but it will index only 255 first characters from those fields. Which means that of you type 2 titles of 255 same characters and then something different - it will give you an error of doubling.

Answer 4

If you have the table created already, go to the structure for it in PhpMyAdmin, select the two columns (title, description) with the checkboxes on the left. then click the U icon in the row of controls under the table description. That will add a unique index over the two selected fields.

Quassnoi's comments about index length and using a hash alternative are spot on. Also note that UTF-8 text columns require three bytes per character in the indexes, so indexing UTF-8 text columns is quite expensive.