cornacum Posted August 15, 2023 Share Posted August 15, 2023 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted August 15, 2023 Share Posted August 15, 2023 id values should always be unique. 1 Quote Link to comment Share on other sites More sharing options...
cornacum Posted August 15, 2023 Author Share Posted August 15, 2023 (edited) @Barand I provided poor example. It will be user ID and timestamp. They will not be unique fields. Edited August 15, 2023 by cornacum Quote Link to comment Share on other sites More sharing options...
Barand Posted August 15, 2023 Share Posted August 15, 2023 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. Quote Link to comment Share on other sites More sharing options...
cornacum Posted August 16, 2023 Author Share Posted August 16, 2023 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 ? Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted August 16, 2023 Solution Share Posted August 16, 2023 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. Quote Link to comment Share on other sites More sharing options...
cornacum Posted August 17, 2023 Author Share Posted August 17, 2023 I wasn't aware that you can create unique index with multiple columns. Thank you Barand Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.