• Welcome to the SQLServerCentral forums.

    This should be easy to sort out but it would help if you could provide some DDL, a few rows of sample data and an example of what you (in this case) don't want to allow. Note the link in my signature about getting help -its very useful. In the meantime, here's a high-level explanation of how to tackle this:

    You are correct that a scalar function is the way to do what you want. The way I tackle this type of thing is to first create a scalar function that looks like this:

    CREATE FUNCTION dbo.fnCheckSomething()

    RETURNS BIT AS

    BEGIN

    DECLARE @isbad bit = 0;

    IF EXISTS (< condition that you don't want >)

    SET @isbad = 1;

    RETURN @isbad;

    END

    Then your CHECK constraint would look like this:

    CHECK (dbo.fnCheckSomething() = 0)

    Be warned that scalar functions usually hurt performance. Just the presence of a scalar udf on a table (either by way of CHECK constraint or computed column) often causes the query optimizer to create a poorer execution plan than it would otherwise - even when that column in question is not referenced.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001