CODEWITHSUNDEEP
phpmysql
jayavel
jayavel

Reputation: 81

mysql unique for multiple columns

When payment happen, sometimes its captured double entry in table. I want to ignore double entry capture so i want to insert records when these created, user_id, amount fields should be unique. How do i make it ? Below is my table.

CREATE TABLE `transactions` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
`user_id` int(20) NOT NULL,
`project_id` int(20) DEFAULT NULL,
`foreign_id` int(20) NOT NULL,
`class` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`transaction_type_id` int(20) DEFAULT NULL,
`amount` float(10,2) NOT NULL,
`description` text COLLATE utf8_unicode_ci,
`payment_gateway_id` int(20) DEFAULT NULL,
`gateway_fees` float(10,2) NOT NULL,
`is_old` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=266 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Upvotes: 1

Views: 76

Answers (2)

Drew
Drew

Reputation: 24959

To strictly answer your question, you create a unique composite key on the combination of those 3 columns. That way no two rows can exist with a combination of the 3 of them in the composite index.

CREATE TABLE `transactions2` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
`user_id` int(20) NOT NULL,
`project_id` int(20) DEFAULT NULL,
`foreign_id` int(20) NOT NULL,
`class` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`transaction_type_id` int(20) DEFAULT NULL,
`amount` float(10,2) NOT NULL,
`description` text COLLATE utf8_unicode_ci,
`payment_gateway_id` int(20) DEFAULT NULL,
`gateway_fees` float(10,2) NOT NULL,
`is_old` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
unique key(created,user_id,amount) -- <------------------- right here
);

insert some data to test it:

insert transactions2 (created,modified,user_id,project_id,foreign_id,class,
transaction_type_id,amount,description,payment_gateway_id,gateway_fees,is_old) values
('2009-01-01 12:00:00','2009-01-01 12:00:00',666,1,1,'a',1,100,'desc',1,12,1);

-- inserts fine (above)

Try it again with exactly the same data:

insert transactions2 (created,modified,user_id,project_id,foreign_id,class,
transaction_type_id,amount,description,payment_gateway_id,gateway_fees,is_old) values
('2009-01-01 12:00:00','2009-01-01 12:00:00',666,1,1,'a',1,100,'desc',1,12,1);

-- error 1062: Duplicate entry

-- change it barely:

insert transactions2 (created,modified,user_id,project_id,foreign_id,class,
transaction_type_id,amount,description,payment_gateway_id,gateway_fees,is_old) values
('2009-01-01 13:00:00','2009-01-01 12:00:00',666,1,1,'a',1,100,'desc',1,12,1);

-- inserts fine

Also, use ENGINE=INNODB. Read about that Here.

Please read Mysql multi column indexes a.k.a. composite indexes.

Lastly, the concept of what you are talking about is not far off from Insert on Duplicate Key Update. Just throwing that reference out there for you.

Upvotes: 3

Subin Chalil
Subin Chalil

Reputation: 3660

You can achieve the same using ,handling at the time of inserting,

You can try WHERE NOT EXISTS with INSERT.

Something like this.(You need to specify column name who has NOT NULL constraint,i missed all those columns)

INSERT INTO table_name(`created`,`user_id`,`amount`) VALUES =
     '$created','$user_id','$amount'
    WHERE NOT EXISTS
    (SELECT * 
        FROM table_name 
           WHERE created ='$created' AND user_id='$user_id' AND amount='$amount')

Hope this helps.

Upvotes: 0

Related Questions