mysql code for only unique ("new") values?

[partha]

I want a mysql table to only accept INSERTs when the value being inserted on a particular column doesn't already exist in the table. Basically I want a particular column to only have one of each value. I want to protect against the same value accidentally being inserted twice

[Salsa]

Making the column the PRIMARY KEY for the table would prohibit duplications.

[partha]

well I already have a different primary key that's just an ID number

[lovethecoast]

Without writing a stored procedure, only way I know of is by use of keys. In SQL Server, you can create multiple keys with unique constraints -- might check to see if you can do that in MySQL.

Addited: You can also use functions in SQL Server -- these may be available in MySQL as well.

And of course, you could just write code to do it all for you. One thing I've done is use a "random key" generator to plug unique values in and have also used a 64bit guid.

If you follow this method, and are wanting, say, a unique numbered list, you could write a quick function that generates a random 3 byte string and then append that to your number (IE: 1-ie3, 2-dss, 3-g34, etc). That way you could still use an "order by" statement and the odds of having the same unique data left of the dash *and* after the dash is very remote.

[coopster]

In SQL Server, you can create multiple keys with unique constraints -- might check to see if you can do that in MySQL.

Certainly. It's an SQL standard. Give the column a UNIQUE [dev.mysql.com] constraint.

A UNIQUE constraint defines that particular column as unique and it is satisfied if no two rows in the table have the same non-null values in the UNIQUE column.

[lovethecoast]

Coopster,

The more I read about MySQL, the more I wonder why I'm paying so much for so many SQL Servers LOL.

My biggest fear is how well it could handle our load -- how big of a machine would I need to process 60 million queries a day with MySQL? (we have multiple sites doing that many queries)

Thanks

S

[danmccarthy]

lovethecoast:

While I can't answer your question specifically, I'd like to point out that Google uses MySQL as its primary database. So it can't be all that bad, right?

[partha]

when I try to add UNIQUE to the column (of type tinytext), it says:

#1170 - BLOB/TEXT column 'artist' used in key specification without a key length

[ergophobe]

MySQL does not index entire text/blob fields, but only the first part to a length you specify. When enforcing the "unique" constraint, I believe it's basically checking against this index, not the entire text field.

I'm not sure I would dare specify unique on a partial index of text field, though, since what if the first 255 characters are not unique because many entries start with the same boilerplate text? You could get false positives that would prevent data entry for perfectly valid data.

Without knowing more about what you're trying to do, it's hard to give too much advice, but I would want to find unique records using criteria other than the text field if possible.

What data are you storing in there?

[Salsa]

Following on ergophobe's reply, since TINYTEXT allows for only 255 bytes, anyway, why not change the column type to VARCHAR(255)? That should allow you to make it UNIQUE without having to specify an index length.

[ergophobe]

I didn't notice that it was of type tinytext. I've never really understood what the point of tinytext is.

so actually, your values could be unique either way, because you can index up to 255 chars of a text field if I'm not mistaken. Like Salsa said, though, I would convert.