How work UNIQUE + INSERT IGNORE?

Question

I have this table :

CREATE TABLE `recent_adds` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `trackid` INT(11) UNSIGNED NOT NULL,
    `user` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'

And I'd like to insert data only when the pair trackd/user is not already inserted in the table.

I know there is a sort of UNIQUE + INSERT IGNORE for this kind of problems, but in fact I don't really understand how it works.

If I do this command :

ALTER TABLE recent_adds
ADD UNIQUE INDEX unique_name (`trackid`, `user`);

where I see that these fields are UNIQUE? On the mysql code I don't see it

Also, after this, can I remove the id field?

Answer 1

You need to add a unique index, then use insert ignore instead of a normal insert. As for the id field, that's up to you, but I would keep it:

ALTER TABLE recent_adds
ADD UNIQUE KEY recent_adds_unique_idx (trackid,user);

INSERT IGNORE INTO recent_adds (id,trackid,user)
VALUES(NULL,...,...);