0

I'm using Google Apps Script to update a MySQL table from Google Cloud SQL and I don't want to insert duplicate values, for example:

If I have the following table with a record 

+----+--------+-----------+-------+
| id | name   | address   | phone |
+----+--------+-----------+-------+
|  1 | John   | Somewhere | 022   |
+----+--------+-----------+-------+
|  2 | Snow   | North     | 023   |
+----+--------+-----------+-------+

Then I should not be able to execute a query that inserts a new record where 

name=John, 
address=Somewhere,
phone=022

or 

name=Snow,
address=North,
phone=023

This is my current code that inserts new records to the database:

  var stmt = conn.prepareStatement('INSERT INTO entries '
      + '(name, address, phone) values (?, ?, ?)');

  stmt.setString(1, "John");
  stmt.setString(2, "Snow");
  stmt.setString(3, 022);
  stmt.execute();
Mustofa Rizwan
  • 10,215
  • 2
  • 28
  • 43
Valip
  • 4,440
  • 19
  • 79
  • 150
  • Why don't you use a [unique index](http://dev.mysql.com/doc/refman/5.7/en/create-index.html) in the MySQL table? Then you can try/catch your insert and check for the error... – mTorres Aug 25 '16 at 10:16
  • I'm using an unique index, but it is auto incrementing. I can have the case where 2 records have the same name or the same address or the same phone, so the insert operation should fail only when there is a record with the same data on these 3 columns. – Valip Aug 25 '16 at 10:18
  • You should use an index for the name not for the id (if you want to provide a unique combination of name, address, phone values, then you have to create an index for the 3 fields) – mTorres Aug 25 '16 at 10:19
  • If I use an index for the name then the insert query will fail if there already exists one record with that name, right? – Valip Aug 25 '16 at 10:21
  • Yes, that's the point, isn't it? – mTorres Aug 25 '16 at 10:22
  • No, there is specified in my question that the insert operation should fail only when there is a record that matches the data from all columns, basically a record identical with the one from the query. – Valip Aug 25 '16 at 10:24
  • Is it possible to do this? – Valip Aug 25 '16 at 10:31
  • Of course it is, that was my point :-) See [this question](http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql) – mTorres Aug 27 '16 at 09:45

0 Answers0