Posts

Showing posts from November, 2019

Check constraint that is a field has a particular value ensure that another field must be filled in.

Image
DB time! So this post regards "normalisation", or lack of, permitting me to be more lazy, and have simpler databases - what's not to like? I had a DB table "users", and two distinct classes of user:  Adult and Child.  Now the business logic demands that the Adult users have an email address entered, whereas the child user doesn't necessarily need one. The Boyce-Cod or whatever blah blah method would be to have 2 tables, but that means loads of JOINs, the inability to view the entire user list in one place, and more logic everywhere. I'd prefer to have 1 table "Users", but how to enforce the business logic above? So I've discovered a way - using CHECK CONSTRAINTS in MS SQL 2012+ The "redmouse" is a child user, it is optional whether he has a email address or not. ([role]='Adult' AND [emailaddress] IS NOT NULL OR ([role]='Child' AND [emailaddress] IS NOT NULL OR [role]='Child' AND [ema