When he reviewed the database schema he stated that all foreign keys and other such constraints should be removed as this is business logic and should be applied within the business layer.

Then he’s an idiot, and some excerpt from your codebase is likely to end up on The Daily WTF someday. You’re absolutely right that his approach doesn’t make sense, and frankly neither does his explanation.

Try explaining to him that referential integrity constraints are not “business logic”; they’re a correctness standard with its own built-in verification. Business logic is about what you do with the data; integrity is about ensuring that the data itself is not corrupt. And if that doesn’t work… well, he’s in charge. You can either go along with his plan and try to mitigate the damage somewhat, or start looking for a better place to work. (Or both.)

I’ve yet to have a reason to create a foreign key

-Joel Spolsky

Blanket statements aside, it is worth acknowledging there are legitimate and strong reasons to chose to not use uniqueness or foreign keys constraints. Most go something like this:

• Performance. Doing integrity checks with atomic transactions is not free. And frequently, the database is most difficult part of your architecture to scale. Perhaps you already do the checks in your application logic, and would rather not incure a second performance hit.
• Lack of need. Perhaps your data is dirty, and you are okay with that. This depends very much on what you are doing.

See also What’s wrong with foreign keys?

But those don’t match the reasons in your question.

This is business logic and should be applied within the business layer.

This reason is a bit odd. Uniqueness and other integrity constraints are very much the domain of an ACID database. Your application code cannot approach the atomicity and integrity guarantees of SQLServer. If you need strong data integrity, you’d be foolish to ignore the database.

A second argument is that they want to adopt the NoSQL approach to their data storage and so do not want such restrictions in place.

The second reason isn’t really a reason; it’s a conclusion. Though it is true that constraints are a trade-off between correctness and performance, and NoSQL databases bias towards the latter.

Perhaps your system architect has these legitimate reasons in mind, and you misheard. Or maybe he’s a blathering idiot.

In any case, there are valid (though not universal) reasons to refrain from using uniqueness and foreign key constraints.

P.S. If you conclude that you don’t want foreign keys, it’s still okay to use a relational database. As a generalization, relational databases are more mature than their NoSQL counterparts. As a single node, they can even be faster, and a NoSQL abstraction can be built on top of them.