1

I have a table below :

+-----+-------+------+------------+
| ID  | RefID | Type | EventTime  |
+-----+-------+------+------------+
| 101 |   228 |    1 | 1437195633 |
| 102 |   228 |    5 | 1437195633 |
| 103 |   228 |    1 | 1437195633 |
| 104 |   228 |    1 | 1437195442 |
| 105 |   228 |    1 | 1437195442 |
| 106 |   228 |    5 | 1437195442 |
| 107 |   228 |    1 | 1437165634 |
| 108 |   228 |    5 | 1437165442 |
| 109 |   228 |    1 | 1437165634 |
| 110 |   228 |    5 | 1437165442 |
+-----+-------+------+------------+

In that I want to stop inserting duplicate data based on the columns RefID,Type,EventTime only when value of Type = 1.

In the above table ID pair is duplicate (101,103), (104,105), (107,109).

If now I will insert another data say :

INSERT INTO table VALUES('',228,1,1437165634);

Then it should not insert. I am checking while inserting into that table but that is not working as I have checked at the same time 2 insert query is happening, I need to stop it using UNIQUE key constraints.

Satender K
  • 571
  • 3
  • 13
  • 1
    can you add your code as well. And are you using any framework or just native php – gecco Aug 19 '15 at 08:52
  • So add the unique composite key to the 3 columns you mentioned, where's the problem exactly? – N.B. Aug 19 '15 at 08:59
  • Do you want add unique index and remove duplicates, right? – Nightw0rk Aug 19 '15 at 08:59
  • I want to add unique key based on the value of "Type" columns when it is 1 then only that constraints should work and I am using Yii framework for development. I have placed check twice for duplicate data not to insert into table at Model level while saving and before saving but the data is coming so frequently that it is not allowing the duplicate data to insert. Difference between two record time may be 100 ms or 500 ms – Satender K Aug 19 '15 at 09:02

3 Answers3

0

You need change DB architecture. Add table with unique index by RefId, here you will write record with Type "1". In your Yii model in method beforesave check your Type if is 1 to write in added table otherwise write to old table. And you need change beforefind method

And I'm sorry for my english

Nightw0rk
  • 411
  • 3
  • 9
0

Please try: 

public function unique($attribute, $params)
{
    if(!empty($model_name)){
            $this->addError('RefID', 'RefID already exists! Please choose a different one');
            return true;}
}

as a custom function in before save. and call it for the fields you want in the rule. I'm showing you the template for a single field refID

Ritobroto Mukherjee
  • 111
  • 1
  • 13
  • I have resolved it by using trigger and handled error in Model save(). I have used signal sqlstate in trigger which have resolved my problem – Satender K Aug 28 '15 at 03:29
  • cool but Model save() or u meant function save()? Cause that function save() is usually called in the controller.. and you shouldn't use PDO or SQL statements in controllers,u should work it out within models. Controller should be slick and lesser in size – Ritobroto Mukherjee Aug 28 '15 at 07:47
  • I have added code for it also you can see there. Thank you for your replies. – Satender K Aug 28 '15 at 07:52
0

I have solved it by using trigger as below :

DB Trigger

delimiter $$

drop trigger if exists stop_duplicate $$
create trigger stop_duplicate before insert on table
for each row
begin
  set @found := false;
  if new.Type = 1 then
SELECT 
        TRUE
    INTO @found FROM
        table
    WHERE
        RefID = new.RefID AND EventTime= new.EventTime AND Type= new.Type;

      if @found then
        signal sqlstate '23000' set message_text = 'CUSTOM_MSG_DUPLICATE';
        end if;
end if;
end   $$
delimiter ;

Yii Model Code

public function save($runValidation = true, $attributes = null) {
    Yii::log(__METHOD__.":: Start ", 'info');
    try {
        return parent::save();
    } catch (Exception $e) {
        $errorInfo = $e instanceof PDOException ? $e->errorInfo : null;
        $message = $e->getMessage();
        Yii::log(__METHOD__ . ": errorcode :{$e->getCode()}, errormessage:{$message} ", 'info');
        // Added for handling duplicate entry issue for index
        if ((int) $e->getCode() === 23000 && strpos($message, 'CUSTOM_MSG_DUPLICATE') !== false) {
            return false;
        }
        throw new CDbException(Yii::t('yii', 'CDbCommand failed to execute the SQL statement: {error}', array('{error}' => $message)), (int) $e->getCode(), $errorInfo);
    }
}
Satender K
  • 571
  • 3
  • 13