1

I have a unique restraint on a combination of columns, rather than on a single one. Naturally, MySQL errors if I then try to insert data that violates this restraint.

This is via PHP > CodeIgniter, by the way.

My question is: on inesrt, what is the suggested way of handling this? Should I...

  • be suppressing the error? (#flameme)
  • be running a query first to ensure it doesn't happen?
  • allow the error because it doesn't cause any visible issue to the user?
  • be using replace into rather than insert into? (I believe CodeIgniter's Active Record class doesn't provide access to replace into, though?)

Context:

I have a tags table, which stores tags that users create to categorise their content:

  • id (PK, int, AI, unsigned)
  • tag (varchar 25)
  • user_id (FK, int, unsigned)

A given user is not able create a tag he has previously created, hence the unique restraint is on the combination of the id and user_id columns.

Mitya
  • 33,629
  • 9
  • 60
  • 107

2 Answers2

2
$insert_query = $this->db->insert_string('my_table', $data);
$insert_query = str_replace('INSERT INTO','INSERT IGNORE INTO',$insert_query);
$this->db->query($insert_query);

I think this should do it.

SNAG
  • 2,011
  • 2
  • 23
  • 43
1

Add key with type UNIQUE to tag+user_id in Your MySQL table. After that set handler to ON DUPLICATE KEY at Your query. You needn't to suppressing errors while You can avoid them ;)

Egor Sazanovich
  • 4,979
  • 5
  • 23
  • 37
  • Thanks for this - `insert ignore` proved too attractive to, er, ignore. – Mitya Jun 25 '14 at 18:40
  • @Utkanos no, You shouldn't use it in this way. Check this question http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update – Egor Sazanovich Jun 25 '14 at 20:54
  • Yeah I actually saw that question. I agree with its points; however, in my case, ignoring the warning won't cause any issue. – Mitya Jun 26 '14 at 12:36