For example, a batch load script that inserts multiple rows into a table may call an insert function within a cursor to populate each row into the target table. Meanwhile, a non-technical business user may want to create only one entity via a UI. However, the UI must have validations and helpful messages to to user so the insert works....or won't, but at least user would know what they need to do differently to get to a successful outcome.
I think you have both. You don't want the round trip of having the database throw an exception in this example. So yes, you have the validations within the UI for a higher level of success before submission. However, you still want the proper constraints on the database itself as it is the canonical source for your data. Further, you never know when someone else is going to connect to the database in something that isn't your application.