Mysql primary key when using INSERT INTO ... ON DUPLICATE KEY UPDATE

Question

My table structure is:

CREATE TABLE IF NOT EXISTS `users_settings_temp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userid` int(10) unsigned DEFAULT NULL,
  `type` enum('type1','type2')
  `data` text,
  `date_created` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

What I am trying to do is:

Let say I want to insert a new entry, but I dont want it to be duplicate, after google around, i found this format:

INSERT INTO users_settings_temp(...)
ON DUPLICATE KEY UPDATE data = '{$data}'

I guess the problem is in my table, the primary key => id. How do I alter the table, so that I could use the:

INSERT INTO ... ON DUPLICATE KEY UPDATE

Can I use user_id + type as primary key? If yes, could you please show me how to do it?

What exactly do you want to happen on a duplicate? Throw it out? Change the data to something? — Stefan Mai, Dec 03 '10 at 08:53
Sorry for not specific. I want to Update when its duplicate. I will update my question shortly. — webdev_007, Dec 03 '10 at 08:55

Unreason · Accepted Answer · 2010-12-03T08:59:56.673

CREATE TABLE IF NOT EXISTS `users_settings_temp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userid` int(10) unsigned DEFAULT NULL,
  `type` enum('type1','type2'),
  `data` text,
  `date_created` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`, `type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

When you do it like this then

a) specifying id works

mysql> INSERT INTO users_settings_temp VALUES (1, 2, 'type1', 'keks', 5);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO users_settings_temp VALUES (1, 2, 'type2', 'keks', 5);
Query OK, 1 row affected (0.00 sec)

b) of course primary key is guaranteed to be unique

mysql> INSERT INTO users_settings_temp VALUES (1, 2, 'type2', 'keks', 5);
ERROR 1062 (23000): Duplicate entry '1-type2' for key 'PRIMARY'

c) letting database pull a new id works

mysql> INSERT INTO users_settings_temp VALUES (NULL, 2, 'type2', 'keks', 5);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO users_settings_temp VALUES (NULL, 2, 'type1', 'keks', 5);
Query OK, 1 row affected (0.00 sec)

but will increase them always

mysql> SELECT * FROM users_settings_temp;
+----+--------+-------+------+--------------+
| id | userid | type  | data | date_created |
+----+--------+-------+------+--------------+
|  1 |      2 | type1 | keks |            5 |
|  1 |      2 | type2 | keks |            5 |
|  2 |      2 | type2 | keks |            5 |
|  3 |      2 | type1 | keks |            5 |
+----+--------+-------+------+--------------+
4 rows in set (0.00 sec)

NOTES:

You should think if your id should still be autoincrement or not. Also, can not think of a reason why date_created should be int(11) instead of datetime

I convert the time to seconds using strtotime() and store the time into database. I follow some tutorial on the internet. Not sure if its a good practice. — webdev_007, Dec 03 '10 at 09:36
@webdev_007, it is not, but it is not so simple either. Determining the domain/type in the database determines *all* further usage of the data (all application code, all database code, queries, formatting, transformations, etc... - of course unless there is some data abstraction layer on the application side); therefore it is good to get it right. Rules are not so simple - but normally you should go for natural types, date is date, amount is decimal, etc.. *unless* you have a reason. (also, if you find answer useful you can also upvote it not only accept it) — Unreason, Dec 03 '10 at 12:32

Mysql primary key when using INSERT INTO ... ON DUPLICATE KEY UPDATE

1 Answers1