Why won't this UDF work in this check constraint?

Question

I’m trying to use a UDF as an alternative to using a SELECT command within a check constraint. I need to put a constraint on the report table to ensure the signedBy field is the PK of a personnel record for which there exists a record in the auth table with their personnel number and the authType of 8 (which would indicate they are authorized to sign reports). My UDF should check for the existence of such a auth record and return a bit (0 if it does not exist, 1 if it does). I'm working on SQL Server Express Edition.

I've pretty much re-created my situation at SQLFiddle:

CREATE TABLE personnel(
    personnel INT IDENTITY(1, 1) NOT NULL,
    firstName VARCHAR(20), 
    lastName VARCHAR(20),
    login VARCHAR(20) DEFAULT NULL,
    title varchar(20) DEFAULT NULL,
    initials varchar(4) NOT NULL,
    startDate DATE DEFAULT GETDATE(),
    CONSTRAINT PkPersonnel PRIMARY KEY(personnel),
    CONSTRAINT UqPersonnelFirstNameLastName UNIQUE(firstName, lastName),
    CONSTRAINT UqPersonnelInitials UNIQUE(initials)
);
CREATE TABLE authType(
authType INT NOT NULL IDENTITY(1, 1),
authName varchar(50)
CONSTRAINT PkAuthTypeAuthType PRIMARY KEY(authType)
);
CREATE TABLE auth(
auth INT NOT NULL IDENTITY(1, 1),
personnel INT NOT NULL,
authtype INT NOT NULL,
date date DEFAULT GETDATE()
CONSTRAINT PkAuthAuth PRIMARY KEY(auth)
CONSTRAINT FkAuthAuthType FOREIGN KEY(authtype) REFERENCES authtype(authtype)
);
CREATE FUNCTION checkIfAuthorized (@personnel INTEGER, @authType INTEGER)
RETURNS Bit
AS
BEGIN
    Return Case 
        When Exists (Select 1 FROM auth
                     Where personnel = @personnel AND authtype = @authtype)
            Then 1 Else 0
        End
END
GO
CREATE TABLE report(
    report INTEGER NOT NULL IDENTITY(1, 1),
    iteminJob INTEGER NOT NULL,
    reportDate DATE NOT NULL,
    notes VARCHAR(200),
    signedBy INTEGER NOT NULL,
    reviewedBy INTEGER,
    conformance VARCHAR(30)
    CONSTRAINT PkReport PRIMARY KEY(report),
    CONSTRAINT [Reports must be associated with an item in a job.] FOREIGN KEY(iteminJob) REFERENCES iteminjob(iteminJob),
    CONSTRAINT [Must be signed by an authorized signatory.] CHECK(metrologyTesting.dbo.checkIfAuthorized(signedBy, 8))
);

Whenever creating my report table on SQL Server Express, the error message is:

'checkIfAuthorized' is not a recognized built-in function name.

even though in SSMS's Object Explorer, it's clearly there under [MyDatabase]>Programmability>Functions>Scalar-valued Functions

The results on SQLFiddle:

An expression of non-boolean type specified in a context where a condition is expected, near ')'.

...are even more perplexing to me. The UDF is set up to return a bit.

Please refrain from recommending I use triggers instead.

Answer 1

The error is because the expression in a CHECK constraint must result in a boolean value (though there is no BOOLEAN datatype in SQL Server). Yours returns a bit value.

You can technically solve this by changing your CHECK constraint to:

CHECK(dbo.checkIfAuthorized(signedBy, 8) = CAST(1 AS bit))

but this whole method - of subqueries in CHECK constraints - is a bad idea in most cases.

Test (that the table can be defined) in http://sqlfiddle.com/#!18/e5501/8

Test that the although the constraint is checked during INSERTs into table report, it is not checked when the referenced table (auth) is updated, so the constraint will fail in some scenarios to enforce what I think is designed to do: http://sqlfiddle.com/#!18/eb49d3/14

---

The reason for the "bad idea" is that CHECK constraints should only use values from a single row. They really are "row constraints", at least in current implementations of most SQL DBMSs and certainly in SQL Server.

That's why you can't put a subquery in a CHECK constraint. I know, you can still bypass this limitation with a UDF (as you have shown!) with a subquery to access other tables but it does not mean that it will work as expected.

The CHECK is only checked when rows are inserted or updated on the table with the constraint but not when the referenced tables are updated (when the referenced rows are updated or deleted).

For example, Hugo Kornelis in Snapshot isolation: A threat for integrity? (Part 4) by Hugo Kornelis where he discusses a constraint very similar to yours in relation to snapshot isolation, states:

Note that this constraint offers only partial protection: nothing prevents you from deleting rows from the Customers table, even if they are referenced by type A orders – you will have to take additional steps to prevent that. Only insertions and updates in the Orders table are checked with this constraint – but with snapshot isolation, not even that is reliable anymore.

More details in various blog articles and answers on SO and DBA:

• How are my SQL Server constraints being bypassed? (answers by gbn, @Remus Rusanu and Martin Smith)
• Scalar UDFs wrapped in CHECK constraints are very slow and may fail for multirow updates by Alex Kuznetsov
• SQL Server : Enforcing Data Integrity in Constraints (part 4) - Problems with UDFs wrapped in CHECK constraints
• Be careful with constraints calling UDFs by Tibor Karaszi
• Using a UDF in a check constraint to check validity of history windows (start - end date windows) by Tony Rogerson

And if integrity issues are not enough, there are also the performance problems that UDFs in general bring:

• Another Hidden Parallelism Killer: Scalar UDFs In Check Constraints by Erik Darling
• Scary Scalar Functions by Tom Zíka

---

Note: my assumption for the business requirements is that the constraint holds indefinitely and is not "temporal": a report is signed by a person and that person needs to be authorized, so the signing person should appear in auth with an authtype=8. And once a person has signed some reports, we should not allow this person's signing authorization to be removed.
(see below for a differentiation)

One way to solve this problem without using a UDF in a CHECK constraint is to use a FOREIGN KEY constraint like this:

• add an authtype column in the table with a hard coded value of 8 (or whatever signifies authorized to sign personnel)
• add a FOREIGN KEY to reference auth
• the above also requires an additional UNIQUE constraint on the auth table

Your definitions (only the changes needed)
and tested in dbfiddle.uk

CREATE TABLE auth(
    auth INT NOT NULL IDENTITY(1, 1),
    personnel INT NOT NULL,
    authtype INT NOT NULL,
    -- ...

    -- UNIQUE constraint added
    CONSTRAINT person_authtype 
        UNIQUE (personnel, authtype),
    -- ...
);

CREATE TABLE report(
    report INTEGER NOT NULL IDENTITY(1, 1),
    iteminJob INTEGER NOT NULL,
    reportDate DATE NOT NULL,
    notes VARCHAR(200),
    signedBy INTEGER NOT NULL,
    -- ...

    -- column added
    authorized_to_sign_authtype AS CAST(8 AS INT) PERSISTED,
    -- FOREIGN KEY added
    CONSTRAINT must_be_signed_by_an_authorized_signatory 
        FOREIGN KEY (signedBy, authorized_to_sign_authtype)
            REFERENCES auth (personnel, authtype)
);

---

Now, if the intention of this constraint and the business requirements are to check whether the person signing the report is authorized at the time of the report and only then and we don't mind if the person is later removed from the authorized signees, then you might have a use case where this CHECK constraint makes some sense (and my suggestion above definitely wouldn't).

If this is properly documented (that the constraint is to be valid only at the time of insertion or update of the report table) and future validations are not meant to pass and we are aware of all the potential side issues (performance - paralellism, integrity with snapshot isolation, etc.), then ok, it's not a too bad idea ;)

I would still prefer to implement this and similar requirements with either (more complex) RI constraints or with stored procedures.