java - 唯一约束和程序查询是否存在的优缺点?

Question

我想要保持数据库记录的不重复,现在有两种方案,一种是给这一个(或多个)字段添加唯一性约束,一种是程序先去查询数据库是否存在给个字段值的记录,再决定是否插入?请问这两种方式该如何抉择?有更好的方式么?

Answer 1

Relying on program to ensure uniqueness is unreliable, because under high concurrency conditions, the following 2 steps will overlap, resulting in repeated insertion:

1. Check whether there is already a record in the database

2. If not, insert record

Answer 2

It is better to choose the unique constraint:

• Simplify application logic.

• When establishing a unique constraint, the system will usually automatically create an index to ensure uniqueness while increasing query performance. (PostgreSQL Documentation)

Adding a unique constraint will automatically create a unique B-tree
index on the column or group of columns listed in the constraint.

Answer 3

The author understands what the purpose of the database is. The database system is developed from the file system. If you follow the second option, you can directly store it as a file. Why use a database? The database is not just simple SQL. As a DBMS, it also provides many other functions, allowing you to concentrate on solving application problems while optimizing the underlying access of the system.