Where to put default values and unique constraints, code or SQL server?

Question

We are designing a new database and I would like some input in where to put stuff like default values. There are 3 scenarios:

1: New values, created_date field. Should the column have a default value when you do an insert?

2: Updated values, updated_date fiels. I've been thinkin about implementing a trigger that sets this to getdate(), other option is in code.

3: country table with country_name, should we enforce a unique constraint directly on the table or make sure code does this?

and last a bit of topic, but we also have an updated_by and created_by (int) in each table that refers to a user_id in the user table. Is it worth the effort to implement this a fk. constraints on all tables?

Answer 1

My best practices:

• For created/last updated dates, it depends on if you are going to use them as part of your business logic -- if they are for auditing only, use a timestamp on the database. If there is any chance you may want to expose them as business properties, they should be assigned in code.

• Unique constraints -- I implement them both in the database and in code. This is an example of the database backing up the validation rules, it doesn't hurt to have them defined in both places. The code definition should catch and handle them, but in the off chance the code fails to catch something, you want the safety net of having the constraint keep the data from being corrupted.

Answer 2

1. created_date, updated_date: If these are purely audit columns, showing changes made and by whom - a trigger is appropriate. If your data model relies on these (maybe the record with the last updated_date is considered current) then your app should do it. Your app can then make decisions about what should be current, instead of having it hardcoded into the schema that it's the "last inserted record, according to the server's current clock settings".

2. Country table: Yes, use a unique constraint. Your data (and hence, your queries) won't make sense without it...you'll get unexpected join matches, and you won't be able to enforce referential integrity. Your app can then reliably depend on it being uniquely named (eg., by storing them in a SortedList) as well.

3. Foreign Key to User table Yes. If it's worth saving the data in the first place, it's worth making sure that it's valid.

Answer 3

Constraints must go in the database at a minimum. If you have to replicate them in code (or infer them from the database) to provide a better user experience, so be it.

The database must be consistent. I have wasted so much time tracking down issues that are the result of "invalid data".

These are solved by adding the appropriate constraint and fixing whatever app created the invalid data. I would much rather have 10 tickets because someone can't save an invoice (easy to trace), than one ticket because somehow a report got a weird value which stemmed from bad invoice data.

Answer 4

Put as much as possible in the database. You can still manipulate/view from your application if necessary, but this helps with both data integrity and future application versions (i.e., you won't have to replicate the logic for your 'web app' version down the road).