Skip to main content

Posts

Showing posts from November, 2019

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

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 [emailaddress] IS NULL))

Al…