区分不同密钥的重复条目

Question

I have a table that holds connections between class rooms and students. A student can only have one entry in the table. Every student has a number in the class room which has to be unique to that class room. I have set up the indexes like this

Columns: classroom_id, user_id, user_no

Primary key (classroom_id, user_id)
Unique key (classroom_id, user_no)

So far so good. School administration has the right to add students to class rooms and there are two scenarios I'm having difficulty telling apart. One is when a student is already in the table, i.e. has an assigned class room, other one is when there is already a student in the class room with the given number.

In my php code here's how I handle the first scenario

try {

    $db->prepare($sql)->execute(...);

    return true;
} catch (\PDOException $ex) {

    if ($ex->errorInfo[1] === 1062) {
        \Data::setError('student_id', 'This student already has an assigned class room');
        return false;
    }
}

The problem is that both, existent student (in the table) and existent number throw an error with code 1062 Duplicate entry. The only thing I saw I could possibly use to differentiate the two cases is the third element in the errorInfo property of the exception which has a verbose explanation of which key caused the query to fail. I didn't want to do this because this way I will be creating a dependency between my application and the key name which could possibly cause the program to stop working properly and could cause a lot of trouble in future when I forget about this, or god forbid if anyone else is maintaining it at the time.

Sending an additional query just to find out which case it is is not a good solution.

To clearify, the question is: How can I differentiate the two errors with the same code?

EDIT: Basic problem solving, I just went ahead and set in the error message that either one of those could be the error, however this is quite an interesting problem that I'd like to find the solution to, so I won't remove the question.

Answer 1

Another possibility is that the insert would have violated both of the unique keys. The error from MySQL will identify just one.

To find out, you'd really have to run another query to see which row(s) are already in the table.

  ( SELECT 'classroom_id and user_id already exist' AS reason_
      FROM mytable t
     WHERE t.classroom_id = :classroom_id
       AND t.user_id      = :user_id
     LIMIT 1
  )
  UNION ALL
  ( SELECT 'classroom_id and user_name already exist' AS reason_
      FROM mytable t
     WHERE t.classroom_id = :classroom_id2
       AND t.user_name    = :user_name
     LIMIT 1
  )

Either of the two SELECTs might return a row. If you get two rows back, you don't know if those are from the same row, or from two different rows. You'd need to modify the query if you needed to reveal that.