Unique key and replace statement

Question

I am getting problem while adding multiple rows at the same time I have a mysql table in which Replace multiple rows of the same id

Suppose I have 2 columns 1) offer_id 2)categories By using php script I am replacing all the rows day by day,So I add unique key for the offer_id and categories but the problem is that,when there are the two values containing 1) offer_id=2 and categories = ecomm 2) offer_id=2 and categories = market

my query will run as follows like

REPLACE INTO `affiliate_offer_get_categories_icube` (`offer_id`, `net_provider`, `categories`) VALUES 
(2, 'icube', 'Marketplace'); 

REPLACE INTO `affiliate_offer_get_categories_icube` (`offer_id`, `net_provider`, `categories`) VALUES 
(2, 'icube', 'Ecoommerce');

In above statements I have to add two rows of same 'offer_id' but different 'categories'. but I am getting in result only one row(I have to add values of both categories.)

Answer 1

Sounds like you need your unique index to span over both columns. Drop the unique index you have and create a new one with

CREATE INDEX idx_whatever_name ON your_tablename (offer_id, categories);