On a large CRM-type application I worked on in the past, database constraints were used to enforce stuff like unique login IDs. Yes, some validation could be done in the UI but that was the way it was done.
When a user tries to create a user with a login ID that already existed, the database constraint is encountered and an Oracle exception is thrown. It is caught and and packaged up through the application stack until it becomes an application-level exception. The end user sees a localized, friendly message instead of seeing a raw, nasty ORA error message. I'm not sure how this could be done without checked exceptions.
You can use unchecked exceptions exactly the same way with as checked exceptions. The difference is that you can ignore them and let them propagate up when it makes sense.
A real-world example of your use case:
The Spring framework repackages all SQLExceptions into a custom data access exception hierarchy. So, if you choose, you could catch a DatabaseConstraintException (or whatever the appropriate exception is, I'm not sure off the top of my head), and throw an application specific exception like InvalidLoginId.
When a user tries to create a user with a login ID that already existed, the database constraint is encountered and an Oracle exception is thrown. It is caught and and packaged up through the application stack until it becomes an application-level exception. The end user sees a localized, friendly message instead of seeing a raw, nasty ORA error message. I'm not sure how this could be done without checked exceptions.