SQL Server – Advisability of Check Constraints for Number Range, Year, and Month

check-constraintssql server

I'm busy designing a website and I have a requirement for the following columns in my SQL Server database:

  1. allocation (values from 0 to 100)
  2. year (only)
  3. month (only)

I have read about check constraints and I'm wondering if the following would be feasible / and if it would be the best solution for the above:

allocation  | tinyint | not null | check constraint: 0-100
year        | date    | not null | check constraint: year
month       | date    | not null | check constraint: month

I will be validating from my front end side as well but I heard it's good practice to also design your DB with these restrictions in place where appropriate.

Best Answer

Yes, use check constraints

Yes, you should add check constraints in your database to ensure valid values, as well as checking in your app. The database side makes for a double-check in case your app’s code has a hole or bug. Also, you may use other apps such as db admin tools to interact with your data, so it is good to have the database enforce the rules.

This kind of data validation is exactly the purpose of the check constraints.

Check year and month too

By the way, your year and month columns should not be of data type DATE. Use a small int for them too, and add constraint checks for reasonable year values such as >2016 and for months being 1-12.

ISO 8601

Also consider storing your year-month values as a single column of type text/varchar, formatted according to the ISO 8601 standard: YYYY-MM such as 2017-03. Sorting alphabetically happens to also be chronological.

By the way, Java includes a YearMonth class to represent this kind of value. That class can generate and parse that ISO 8601 formatted string by default with its toString and parse methods.

Avoid keywords as names

Avoid using any of the over one thousand keywords and reserved words used by various databases. Your "year" and "month" names may be problematic.

Simple solution I use is to append a trailing underscore in all my SQL names (catalog, schema, table, column, index, etc.). So, year_ and month_. The SQL spec explicitly promises that no keyword will ever have a trailing underscore.

Related Question