CHECK constraints do not fail when the value is null. If the value is null, the condition of the check constraints usually evaluates to
UNKNOWN and the row is accepted. Only when the condition evaluates to
FALSE, the row is rejected.
Another option is to use a scalar UDF inside the check constraint.
No, no, no. User defined functions are allowed inside
CHECK constraints but there are several problems, often because they are used to implement complex behaviour/constraints, like searching a whole other table. In your case, a UDF would not do anything that can't be done with standard date functions. So don't use a UDF.
The final option I've thought of is to use an
AFTER INSERT/UPDATE trigger to check the date and rollback if the date is invalid.
You could but a
CHECK constraint is simpler. Triggers are far more difficult be tested.
I've spoken to a colleague and he says technically I'm checking a business rule in the database, so this check should be pushed off to the application.
This is a rather "hot" debate about whether business rules should be in the database or in the applications. I find the approach "all business rules in the application" a bit naive. Primary and foreign key constraints implement business rules for example, should we abolish them from our database, too? Another reason for keeping business rules closer to the database is that we may have more than one application using it. We then find implementing the same rules again and again, in possibly different languages/frameworks when it could have been done once, in the database.
One problem exists though with the specific constraint, see below*.
You case could be something like below. Tested at rextester.com:
CREATE TABLE person
( person_id INT NOT NULL PRIMARY KEY,
birthdate DATE NULL,
CHECK (birthdate <= DATEADD(year, -18, GETDATE()))
Inserting null and valid dates succeeds:
INSERT INTO person
(2, NULL) ;
SELECT * FROM person ;
But invalid dates fail:
INSERT INTO person
(3, '20010101') ;
The INSERT statement conflicted with the CHECK constraint "is_adult". The conflict occurred in database "rextester", table "dbo.person", column 'birthdate'.
*: One problem with this particular case/constraint - and not because the column is nullable: one of the function used -
GETDATE() - is not deterministic. It won't - obviously - yield the same value if it is run a day or a year later. It's unlikely that you'll have a problem because once one is over 18, one stays over 18. Age increases by time.
Weird scenario: Someone changes the date of the system running the database and puts it in, say 1986. Suddenly the previously valid rows become invalid. What will happen then? (Frankly I've no idea, have to test that!)
Consider the above and it might be more appropriate to handle this constraint in a procedural way, either in the applications or by handling the table only through
UPDATE procedures that enforce the constraint.