CODEWITHSUNDEEP
mysql
MagisterMundus
MagisterMundus

Reputation: 335

mySQL - Create 4-fields primary key/index to stop duplicates?

I have a table called locations that, among other fields, has the fields: 

IMEI: VARCHAR(15) foreign key references devices.IMEI
latitude: DECIMAL(10,6)
longitude: DECIMAL(10,6)
datetime: DATETIME

Sometimes, my server stores duplicates, i.e all of the fields above are equal, meaning a given device (IMEI) was at latitude,longitude at datetime. I want to block all inserts that have these four fields equal to any other row on the table.

I'm not sure if I should use index or primary key. If it helps, these fields are used on selects/filters all the time.

Any ideas?

Upvotes: 1

Views: 52

Answers (1)

Szymon Roziewski
Szymon Roziewski

Reputation: 1129

Add the composite primary key by altering the existing table

ALTER TABLE locations ADD PRIMARY KEY(IMEI, latitude, longitude, datetime);

Or you can define the key while creating the table

DECLARE @locations TABLE (
...,
PRIMARY KEY(IMEI, latitude, longitude, datetime)
);

Upvotes: 1

Related Questions