However, one of my colleague said this is not a proper design: Each layer should not rely on other layers. And suggested to handle the duplicate key rule in the data access layer also. The reason He had: Imagine this scenario, changing the database to have a plain file instead so we can not leverage the unique keys constraint.
Your colleague is close here. The problem is not that layers in general should not trust each other. In fact, they should trust each other with certain concerns. The problem is that other layers are concerned with this validation because of the fact that the DBMS might change to a system that cannot be trusted with this responsibility.
The question you have to ask yourself is, how likely is it that you will replace your DBMS with a system that cannot enforce this constraint?
If that replacement is not likely, then the only benefits of redundant validations are looser coupling and not having to traverse application layers unnecessarily (think of not validating redundantly as making an API call with a request that you know might be invalid).
Assuming that the risk of DBMS replacement is likely enough to offset the maintenance cost of redundant validations, let's think about each layer:
UI Layer (don't validate)
The only benefit of validating here is the instant feedback to the user, and the drawbacks are the scalability/security issues of needing to expose all existing keys to the UI to perform the validation. While this may be possible if your architecture and data set allow for it, it is probably not ideal unless it is a hard UX requirement that the validation be done locally (and presumably it's not, as it is currently not performing the validation).
Business and Data Access Layers (validate in one of these)
If you're following a repository pattern or something similar for your data-access layer, it's probably a good idea to treat the validation as business logic and put it in the business layer. If you're following the broader data-access layer pattern, however, it might make more sense to separate that data model knowledge from the business logic. (There is some confusion around the differences between these two types of data access layer, and what the responsibilities of a data access layer should be.)
Remember that the only reason we're validating here is that we're assuming that the DBMS cannot be trusted with that responsibility; as such, to maintain valid separation of this concern, you should choose one of these two layers to be responsible for the validation, not both.
Data Storage Layer (validate)
At first glance, it might seem like adding a unique constraint here as you suggested is violating the trust of the business or data-access layer and overlapping concerns too much. However, remember that this layer, being a DBMS, is concerned with data integrity (e.g. normalization). As such, it is an overlapping concern, and the database has every right to enforce such integrity by leveraging constraints.
More practically speaking, this is a prudent way to ensure that no invalid data are created by mistake. Bear in mind that the data access layer may not be the only entity creating/modifying data to this database. The mere fact that human users with database credentials may log in and create data directly is enough to scare me into violating any SoC purism that might exist here (though it's my opinion that none does).
- Separate concerns appropriately, and acknowledge when a concern overlaps layers.
- Use redundant validations when you want to account for future abdication of responsibility or reduce unnecessary layer traversals.
In this particular case, I would add the database constraint and validate in the business layer or data access layer (not both), depending on how you've defined the responsibilities of your data access layer.