Should items like Foreign Keys, Constraints, Default Values, and so on be handled by the database management system (in this case, MS SQL 2005) or the application? I have heard opinions from both sides and I'm honestly not sure which way to go.
There is a chance we will be spanning multiple servers/databases and I don't think Foreign Keys can be used across linked servers. In addition to that, there are some circular references in the database design which prevents me from using
ON UPDATE CASCADE on everything.
The database is MS SQL 2005 (possibly 2008) and all interactions with it should go through the application.