Where to set defaults and unique constraints, code or SQL Server?
We are developing a new database and I would like some contribution to be placed in it, for example, by default. There are 3 scenarios:
1: new values, created_date. Should a column have a default value when inserted?
2: Updated values, updated_data. I was thinking about injecting a trigger that sets this in getdate (), another option is in code.
3: table country with country_name, should I enforce a unique constraint directly on the table or make sure the code does it?
and last bit of a topic, but we also have updated_by and created_by (int) in each table that refers to the user_id in the user table. Is it worth trying to implement this fk. restrictions on all tables?
source to share
-
created_date, updated_date : if these are purely audit columns showing the changes made and by whom - the trigger fits. If your data model depends on them (perhaps the record with the last updated_date is considered current), then your application should do so. Then your application can make decisions about what should be current, instead of being hardcoded to the schema, that it is "the last inserted record according to the current server clock setting."
-
Country table : Yes, use a unique constraint. Your data (and therefore your queries) won't make sense without it ... you'll get unexpected matches and you won't be able to enforce referential integrity. Then, your application can reliably depend on what it is uniquely named (for example, by storing them in a SortedList).
-
Foreign key table for user Yes. If you want to save the data first, make sure it is valid.
source to share
My best practices:
-
For the created / last updated dates, it depends if you intend to use them as part of your business logic - if they are for auditing only, use a timestamp in the database. If you are likely to want to expose them as business objects, they must be assigned in code.
-
Unique constraints - I implement them both in the database and in code. This is an example of a database that supports validation rules, and it doesn't hurt to define them in both places. The definition of the code has to catch and handle them, but secondly, the code cannot catch something, you want the safety net to have a limitation so that the data is not corrupted.
source to share
Constraints should go to the database as a minimum. If you need to reproduce them in code (or pull them out of the database) to provide a better user experience, so be it.
The database must be consistent. I've spent so much time tracking down problems that are the result of "invalid data".
They are resolved by adding an appropriate restriction and fixing that the application generated incorrect data. I would rather have 10 tickets because someone can't keep the account (easy to track) than one ticket because somehow the report got a weird value based on bad account data.
source to share