Duplicated records being inserted in mysql table despite unique index

Question

I am using the redbeanPHP ORM and mysql. I have the following table:

CREATE TABLE `mast` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `note` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `geolocation` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `location` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `zip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `state` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `app` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `UQ_84a93b55f688c94f73092dba1b9590c41a92cbf5` (`app`,`geolocation`)
) ENGINE=InnoDB AUTO_INCREMENT=98 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I want to insert records into the 'mast' table providing they are unique with respect to both of the 2 fields listed above. In other words if either 'geolocation' or 'app' is a duplicate, I don't want to insert the associated record.

I am using following php code to insert the records using rebean:

      $resultBean= R::dispense('mast');
      $resultBean ->import($resultsarray);

               try {

                 $id = R::store($resultBean); // TRY TO INSERT INTO MAST

               } catch (Exception $exc) {

               } 

The insert is occurring except I notice that duplicate records on at least the 'app' field are being inserted. I am getting a normal looking record, while the duplicate has all zero or null values except for the 'app' field which has a duplicate entry.

I don't want the duplicate entries in the table at all. How can I prevent them from being inserted?

Answer 1

if either 'geolocation' or 'app' is a duplicate, I don't want to insert the associated record.

UNIQUE KEY `UQ_84a93b55f688c94f73092dba1b9590c41a92cbf5` (`app`,`geolocation`)

Only prevents the pair app and geolocation from being duplicated.

If you want to prevent either one individually being duplicated, add separate indexes for each i.e.

CREATE UNIQUE INDEX UQ_app ON mast (app);
CREATE UNIQUE INDEX UQ_geolocation ON mast (geolocation);