mysql unique key

I have a table items that holds items that have been select by a user 开发者_开发问答but without a unique or primary key as there no unique records on the table because on every row userID and listID are not unique (user will surely have more than one item) and itemID can be unique/primary as more users might select the same item. What is the correct way to add unique and/or primary key on that table that will make sense?

items table:

CREATE TABLE IF NOT EXISTS `items` (
  `userID` int(20) NOT NULL,
  `listID` int(20) NOT NULL,
  `itemID` int(20) NOT NULL,
  `qty` int(10) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

items data:

INSERT INTO `items` (`userID`, `listID`, `itemID`, `qty`) VALUES
(12, 277, 44, 2160),
(12, 277, 3683, 13500),
(12, 277, 3689, 2700),
(12 277, 9832, 4320),
(12 277, 9848, 540),
(12, 277, 16272, 81000),
(12, 277, 16273, 81000),
(12, 277, 16274, 243000),
(71, 278, 12066, 4),
(82, 279, 44, 2880),
(82, 279, 3683, 18000),
(82, 279, 3689, 3600),
(82, 279, 9832, 5760);

---

You can create an index across multiple columns. In this case, I'd recommend having your index across all ID columns, so that an item would be unique to a user and a list. This should do the trick:

CREATE UNIQUE INDEX unique ON tbl_name (userID, listID, itemID);

---

Declare an extra "id" column in your table, and use auto-increment. See e.g. http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html.

This column doesn't have any meaning, but it can be used to uniquely reference entries in the table (the whole purpose of the primary key).

---

Add a new column

`id` int (20) auto_increment primary key