Check constraint on nullable data, how to approach?

Question

This is one of the tables in my database. Constraints omitted for clarity.

CREATE TABLE [Person].[Person]
(
    [ID]                     INT           NOT NULL IDENTITY,       
    [Forename]               VARCHAR(16)   NOT NULL,
    [Surname]                VARCHAR(32)   NOT NULL,
    [Gender]                 CHAR(1)       NOT NULL DEFAULT 'U',
    [DateOfBirth]            DATETIME      NULL,
    [HobbiesAndInterests]    VARCHAR(256)  NULL,
    [AdditionalInformation]  VARCHAR(512)  NULL,
    [LocalCentreID]          INT           NOT NULL DEFAULT 0,
    [EmergencyContactID]     INT           NULL, 
)

The problem I have involves the date of birth field. If a value is provided, I need to run a check against the date. If a value is not provided, the check does not need to run.

I basically need to check if the person is 18 or older based on the date of birth. If a date of birth isn't provided, then there's no need for it to be checked.

I've thought a few different ways I can address this:

The first is obviously to place a check constraint on the date of birth field but I think this fails if the date is null.

Another option is to use a scalar UDF inside the check constraint.

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.

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.

Is there a 'right' way to approach this problem or is it basically what the developer thinks is best?

Answer 1

Use a CHECK constraint.

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,
  CONSTRAINT is_adult 
    CHECK (birthdate <= DATEADD(year, -18, GETDATE()))
) ;

Inserting null and valid dates succeeds:

INSERT INTO person 
    (person_id, birthdate)
VALUES
    (1, '19500101'),
    (2, NULL) ;

SELECT * FROM person ;

person_id  birthdate
1          01.01.1950
2          NULL

But invalid dates fail:

INSERT INTO person 
    (person_id, birthdate)
VALUES
    (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 INSERT and UPDATE procedures that enforce the constraint.