Both ways are valid in 3-layer development.
Just aligning the concept of three layers (despite being a classical, ancient and consolidated definition, one may not be familiar): the layers are Interface/Presentation, Applying and Database. The history of this definition and function of each layer, if necessary, can be context to another question.
In addition to these two ways (validate either on the application layer or on the database layer), there is also a third option that is validate in both layers.
I explain: depending on the tools you are using, it can be difficult to show a friendly message the user in case the duplicity is rejected only by the database.
Database systems often show messages of the type:
"SQL error 3344: attempt to violate the single index Ix_aaaxxxbb09222_01..."
and rarely these messages are useful to the end user.
Even if you try to give a meaningful name to each index or Constraint, the message can still get very strange - besides that friendly names are not always possible by limitation of the database itself (name size, use of special characters, etc.).
So when to use each of the three options:
Validation only in the database
You can only validate in the database when the chances of breach of constraints are remote or when the Constraint breach error does not affect the common user interface (when the error can only occur in integration scenarios, for example).
If feasible, according to the tools you use, you can also validate only in the database and rely on a routine that translates error messages properly, showing a more user-friendly message to the end user.
Validation only in the application
You can only validate in the application when the application itself is able to ensure integrity at a satisfactory level.
If respect to constraints is the premise of the system, validating only in the application can be a high risk too much to assume, besides the benefits being few because the database validates constraints with more performance than the application.
The most common scenario for not having constraints in the database is the need for high performance, and in these cases the system is modeled not to depend on the guarantee of relational integrity or uniqueness of records.
In-app and database validation
This option is useful to not let the user go too far in his work when even before starting this job no longer has any condition to be persisted.
There may be specific business rules that are worth the effort to check beforehand whether user entries are valid for the database, and warn you in advance that it needs to go another way.
Then, of course, in the act of persistence, the bank’s constraints still come into play to ensure integrity.
Finalizing
It is common for the same system to adopt the three options, validating only in the database, or only in the application (or simply not using constraints) or validating in both layers, depending on each requirement.
Boy, did I like the answer.
– Joel
The MVC is also layer-based as well as this pattern?
– gato
@drmcarvalho Not necessarily. MVC is concerned with isolating business rules from application and interface rules. See: You can develop a Three Layers app and mix in the middle layer, in a single component, the interface settings with the business and application rules. It will still be Three Layers but not MVC.
– Caffé
@drmcarvalho While, on the other hand, you can correctly implement MVC by encoding all components in the middle layer. MVC is more about separating responsibilities into components than layers (be they physical or conceptual layers).
– Caffé
@Caffé in this case I would leave the domain rules only in the Model, different from the layered system that the same rules can be treated and various layers right? This is the separation of the responsibility of each component of the MVC, that?
– gato
@drmcarvalho Even using MVC you can have rules treated in more than one layer. See this description of the MVC: http://answall.com/a/86132/14584. Note that "layer" has not been mentioned. There is no necessary correlation between MVC and "tiers" or "layers", nor similarities, nor differences, nor a specific way of distributing MVC components between layers... They are distinct things. Understand physical layers ("tiers"), conceptual layers ("layers"), and also MVC. Eventually take a look at the DDD layers as well. This should clear up the ideas.
– Caffé