Insert Ignore

[cornacum]

Hi

I need to insert the data in the database but on the condition that the values of two columns combined do not already exist in the table? Is it possible to use inset ignore for this?

example

Current data in the table for id and timestamp.

id  | timestamp

57 | 2023-08-15 09:07:13

It should not be possible to insert again the same values combined;

57 | 2023-08-15 09:07:13

 But it should be fine to insert:

58 | 2023-08-15 09:07:13

57 | 2023-08-16 09:07:13

etc.

If that makes sense?

Edited August 15, 2023 by cornacum

[Barand]

id values should always be unique.

[cornacum]

@Barand I provided poor example. It will be user ID and timestamp. They will not be unique fields.

Edited August 15, 2023 by cornacum

[Barand]

If id in this instance is a foreign key, then you also have a poor column naming convention.

Does your table have a unique constraint on this pair of columns? EG

UNIQUE KEY (id, timestamp)

If so, that will prevent a duplicate being inserted. INSERT IGNORE will prevent the attempt from throwing an error message.

[cornacum]

The table has 3 columns. 

No there are no constraints on user_id and timestamp, so I assume insert ignore will not work.

id - primary, unique

user_id

timestamp

Is there any other way around this?

The only thing I can think of is, before I run Insert Query, run  "SELECT * from table_name WHERE user_id = user_id AND timestamp = timestamp", if I get row count > 0, there is a duplicate and do not insert ?

 

 

 

 

 

 

[Barand]

Just alter the table to add the unique constraint. Run this query...

ALTER TABLE `tablename` 
ADD UNIQUE INDEX `unq_user_date` (`user_id` ASC, `timestamp` ASC);

Checking if it exists first is the worst way to do it.

[cornacum]

I wasn't aware that you can create unique index with multiple columns.

Thank you Barand