-1

I am using Spring Boot, Maven, Hibernate and MySQL

I am trying determine if is better to programmatically find out if a record already exist in the database --> table or if is better to do that by modifying the database table to not allow duplicates.

Any suggestions?

Gibran Castillo
  • 47
  • 9

1 Answers1

0

Modify the database to not allow duplicates. This is the purpose of unique constraints on tables.

Why is this the right solution? The basic reason is race conditions. When you impose a unique constraint, the database does the checking. If you try to do this in the application layer, then there is a small gap of time between the check and the insert . . . and another thread could insert the same value during the time. And, you have a duplicate.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • ..and it's faster and it's less work – johannes_lalala Feb 06 '16 at 02:43
  • @Gordon Linoff that's where transactions come into the game? Shouldn't it a transactional SQL operation avoid duplicity? Checking and inserting in a single transaction, I mean. – Aritz Feb 06 '16 at 11:47
  • 1
    @XtremeBiker . . . Explicit transactions can incur a lot of overhead. It is *possible* to enforce the rules at the application layer. It is faster and simpler to let the database do it. – Gordon Linoff Feb 06 '16 at 12:12
  • Thanks, so interesting. So it's advisable to check for integrity at DB layer (constraints or triggers). Anyway, [transaction management](http://docs.spring.io/spring/docs/current/spring-framework-reference/html/transaction.html) could be handy if performance is not a priority. – Aritz Feb 06 '16 at 13:29
  • How do I specify a composite unique constraint in MySQL table (columns, two to be specific in my scenario)? ALTER TABLE `motedb.profile_has_friend` ADD UNIQUE `unique_index`(`profile_id`, `profile_friend_id`); with id as primary key 'id'. These are the only column is the table: Would this work properly with the ON DUPLICATE KEY clause of INSERT statements? That is, if I were trying to insert a row that conflicted with another row's profile_id/profile_friend_id values, would the INSERT do the actions specified by the ON DUPLICATE KEY clause instead? – Gibran Castillo Feb 06 '16 at 20:22
  • I executed: ALTER TABLE `motedb`.`profile_has_friend` ADD UNIQUE `unique_index`(`profile_id`, `profile_friend_id`); SQL script was successfully applied to the database. But, I am getting an exception in the Java class (hibernate) when it attempts to insert the duplicate – Gibran Castillo Feb 06 '16 at 20:22
  • 2016-02-05 22:36:08.003 WARN 951 --- [nio-8080-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1062, SQLState: 23000 2016-02-05 22:36:08.003 ERROR 951 --- [nio-8080-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper : Duplicate entry '2-1' for key 'unique_index' 2016-02-05 22:36:08.006 WARN 951 --- [nio-8080-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Warning Code: 1062, SQLState: 23000 2016-02-05 22:36:08.006 WARN 951 --- [nio-8080-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper : Duplicate entry '2-1' for key 'unique_index' 2016-02-05 22:36:08.220 ERROR 951 --- – Gibran Castillo Feb 06 '16 at 20:24