How does world practice suggest checking for uniqueness when inserting rows into a database table?

Question

Database

Good afternoon, it was very difficult to formulate the question, but the question is simple.
Imagine that there is an application that has a form with text fields PERSONNUM NUMBER, FIRST NAME, LAST NAME, which inserts rows into a database table.
But in the database, the PERSONNUM NUMBER field must be unique.
The question is, how and in what cases is it more correct and "easier/cheaper" to try to first check with code whether there is such a row in the table and, if it is, report on an existing record, or try to insert directly by the database and expect either an exception with an error (which is then beautifully processed, and beautifully reported to the user) or a positive insertion response.
It seems like an elementary question, but I want to hear opinions and maybe some pitfalls

Answer 1

maybe some pitfalls

Well, there is of course.
Let's say you check from the application if there is a personnel number in the table. Find that it is not and try to insert a line with the next request. But between requests, such a line has already appeared and you still need to handle the exception.
Maintaining data consistency is the task of the database, there is no need to reinvent the wheel here.

Answer 2

It all depends on the specific circumstances. At the database level, checking with a query can be an order of magnitude faster than trying to insert a duplicate. At the application level, due to overhead, the difference will be smaller, sometimes significant.
And then probabilities come into play. How likely is it to run into a duplicate? If such a phenomenon is rare, then you don’t need to bother - make a unique key and immediately insert it. If most of the write attempts fail, you can try to make a request first, and if it fails, generate a new key in the application. But in this case, another solution suggests itself: generate the key directly during insertion using the database tools and return it as the result of the operation.