Duplicate primary key exception using ActiveRecord::link()

Yii 2.0 General Discussions
1

I got this error apparently from calling ActiveRecord::link().

Error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘17888-11810’ for key ‘PRIMARY’

The SQL being executed was: INSERT INTO show_persona (show_id, persona_id) VALUES (17888, 11810)

show_persona is the junction table involved in the link and its PK is (show_id, persona_id).

Must I check if the relation between the records already exists in the junction table before calling join()?

2

You need to have a way to insure unique ID’s in your primary key attributes. The easiest way since you are using some sort of SQL is to use the auto increment feature on your primary key fields.

Edit: this isn’t a good solution for this issue. I didn’t read the whole problem and just the dB error. Sorry for any confusion.

I’d figure out why you’re getting duplicate entries in the first place. Since this error shouldn’t really ever happen I’d use a try catch like you are. Since you mentioned performance is key I’d limit the active query usage and manually do the linking.

3

unique id’s is really bad idea, you are on the right track check for existing records or delete if there are any before you call link.

4

ActiveRecord::link() involving a junction table could be a little tricky, since it doesn’t check the already existing record before inserting the link record by design.

I usually do something like the following:




$show = ...       // the show in focus

$persons = [...]; // selected persons

$show->unlinkAll('persons', true);

foreach($persons as $p) {

    $show->link('persons', $p);

}

Or, actually, I prefer the following:




$show_id = ...

$person_ids = [...];

ShowPerson::deleteAll(['show_id' => $show_id]);

foreach($person_ids as $pid) {

    $sp = new ShowPerson();

    $sp->show_id = $show_id;

    $sp->person_id = $pid;

    $sp->save();

}

Since I tend to use a list box with multiple selection (which holds the selected ids) for handling this kind of relation, the code above works nice for me. :)

5

In my case the chance that a link already exists is very low but i need to add many million such links quickly so performance matters.

I decided not to check for existing links or to delete any such links and instead to wrap the link() call in a try/catch.

6

that is design choice by wrapping it in a try/catch if it blows you run a query again, indirectly also related to performance. Another solution could be don’t make your column(s) to be primary keys just add simple index on both columns and keep inserting you don’t need the try/catch and later on you may run a cron job that cleans your table and removes the dups.

7

If performance really matters, I would do it without using ActiveRecord, making use of the lower layers of db handling feature.

As alrazi suggests, the following doesn’t seem to be an efficient code:




$show = ...       // the show in focus

$persons = [...]; // selected persons

foreach($persons as $p) {

    try {

        $show->link('persons', $p);

    }

    catch($e) {

        ... ignoring it, probably ...

    }

}

Note that you can’t wrap the ‘foreach’ block as a whole in the try-catch section, because an exception may break the loop earlier.

But why do you get duplicated entries?