Column with URL unique

Question

I have a MySQL database, in which I have a table with website addresses. Visitors of the website can send in links which I add to my site.

To prevent links to the same page I want to make the column with the URLs unique. How can I do that?

I found something like below but is there any way to set this inside phpmyadmin?

INSERT INTO `table` (value1, value2) 
SELECT 'stuff for value1', 'stuff for value2' FROM `table` 
WHERE NOT EXISTS (SELECT * FROM `table` 
WHERE value1='stuff for value1' AND value2='stuff for value2') 
LIMIT 1 

Answer 1

There's several solutions to problems like this, but my favorite is to use a checksum. here's one example ( using a high bit checksum stored as binary data for efficiency's sake):

create table urls ( 
    id int unsigned not null auto_increment primary key, 
    binsha512 binary(64), 
    url text, 
    unique key(binsha512), 
    duplicates int unsigned not null default 0 ) ;

set @newurl = "http://danf.us"; -- so we only have to send it once

insert into urls set 
    url=@newurl, 
    binsha512= UNHEX( SHA2( @newurl,512)) 
    on duplicate key update duplicates = duplicates + 1;

You'll either insert the URL with checksum, or increment the duplicate count. Of course this can't be accomplished without changing the schema, and the insert query, which may be undesirable. But it has the distinct advantage of handling arbitrary length URLS.

Answer 2

You should be able to make columns unique with executing SQL code in your phpMyAdmin environment:

ALTER TABLE  `table` ADD UNIQUE ( `uniqueColumn`, `anotherUniqueColumn` )

Then, when you will try to insert data to this columns and it won't be unique you will get an error.

Answer 3

Of course you can make unique columns in PHPmyAdmin, and not only by writing SQL, but by using the graphical interface, which is easier. Also, you can make your own script to check if that value is inserted already in the table. You just have to use a function that fetches the values of the rows in conjunction with a loop and compare the values retrieved with the one that the user tries to submit. It works pretty fine, at least with a small database.

Answer 4

As Dan Farrell mentioned in a comment, your question lacks precision: it would have been much clearer if you had given us the schema you were working with (what are the columns?).

Additionally, as Daniel mentioned in his answer, if you want to have a unique column or a unique combination of columns, you should use a UNIQUE index to start with.

Now with that said, relying on MySQL reporting errors for business decisions is not quite right, and not needed. Indeed, MySQL provides two commands that let you decide what to do when an insertion violates a unique index, and it should be used instead of having MySQL throw an error. You can refere to MySQL documentation on INSERT for more

1. INSERT IGNORE INTO ...: with this syntax, MySQL will insert the row only if no unique key is violated, i.e. in your case if the URL is not already in the table. You'll get something like

   INSERT IGNORE INTO `yourtable` (field1, field2, ..., url)
   VALUES ('f1', 'f2', ..., 'http://example.com');
   

   Upon executing the query, MySQL will tell you how many rows were affected, thus if the url was already present, it will tell you no row was affected.

2. INSERT INTO ... ON DUPLICATE KEY UPDATE ...: with this syntax, you will be able to update an entry instead of inserting a new one if the url already exists; useful for example if you had a counter field storing how many times the url has been submitted by your users

   INSERT INTO `yourtable` (last_submitted_by, counter, url)
   VALUES ('joe', 1, 'http://example.com')
   ON DUPLICATE KEY UPDATE
     counter = counter + 1,
     last_submitted_by = VALUES(last_submitted_by) ;
   

   Note that in the UPDATE part you can reference values you specified in the INSERT part with the VALUES(..) construct (in my example this would be used to maintain a reference to the last user who submitted this url.

Update (after a comment on an other answer)

Of course there is a limit to the size of fields you can use in an index, and for text fields you must specify the length. This answer on another SO question gives details on the limit, but even with the lower limit (1000 bytes for MySQL), that would be plenty enough for urls uniqueness (that would be 500 characters if stored in unicode, which you don't really have a reason to do for urls, although now you might encounter some with special characters now that non ascii characters are available in domain names (outside domain names, non ascii characters should be url encoded anyway).

Answer 5

I changed the columntype from TEXT to VARCHAR with a length of 200. After that i could set it to unique.