Avoiding duplicate entries (MySQL)

Databases
1

I haven’t started this yet so all suggestions are welcome.

col_1 = integer between -180 and +180
col_2 = integer between 3000 and 12750
col_3 = integer between 1000 and 45000
col_4 = integer between 0 and 3
col_5 = timestamp(now)

I’m receiving data from a central server that I don’t control and it must put it into a local MySQL database, but without any duplicates. A duplicate is when columns 1, 2, 3, and 4, are all identical to the incoming data. In that case I just want to update the timestamp, not insert a new line. If only 3 or less of the first 4 columns are matches I want to insert a line. What is the best way to go about this?

2

Make col_1 through col_4 the primary key and use INSERT … ON DUPLICATE KEY UPDATE :slight_smile:

3

How can you make 4 columns a primary key? I thought a primary key was on one column, not a combination of columns.

4

No, you can use multiple columns if you like.

Something like


CREATE TABLE mytable (
   col_1 INT,
   col_2 INT,
   col_3 INT,
   col_4 INT,
   col_5 INT,
   PRIMARY KEY (col_1, col_2, col_3, col_4)
)
5

From the manual:

A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attribute in a column specification. Doing so only marks that single column as primary. You must use a separate PRIMARY KEY(index_col_name, …) clause.

What he says ^^
:slight_smile:

6

:slight_smile: Thanks :wink: