SQL Server Performance

Check Constraint Using SELECT

Discussion in 'T-SQL Performance Tuning for Developers' started by BDRichardson, Jul 11, 2006.

  1. BDRichardson New Member

    Hi,

    I suspect that I can only achieve the desired outcome of my problem by creating a Trigger on the table. However, I wonder if I can achieve the same outcome with a Check Constraint on the table.

    I have a contact table which has a Primary Key column (ContactId), a Surrogate Key column for the Customer (CustomerId) and a Contact Type column (ContactTypeId). I have a requirement to ensure that for any Customer, that a maximum of only one Contact Type is permitted for some of the Contact Types. I cannot create a Unique Constraint on the CustomerId and ContactTypeId because the rule is only specific to a few Contact Types. All other Contact records do not have to comply to the rule, i.e. a Customer may have any number of Contact records for the remaining Contact Types.

    I need to ensure that the rule is enforced for both inserts and updates to the Contact table.

    Is this possible in SQL Server 2000? I know you can create Check Constraints which compare column values against values, but is it possible to compare the proposed values against existing ones as per my rule. According to MSDN for SQL Server 2005 you can use a Function within a Check Constraint, but it would seem that you cannot with SQL Server 2000.
  2. Adriaan New Member

    I wouldn't really bother with constraints for this. Also, those ugly constraint violation messages do not say anything about what has caused the violation. If you use a trigger, specific information can be fed back through RAISERROR.
  3. BDRichardson New Member

    You may have a point and I will consider it.

    Have you any ideas why when using a User Defined Function in the Check Constraint I get an 547 error:

    ALTER TABLE statement conflicted with TABLE CHECK constraint 'CK_Contact_PrimaryContactTypeUniqueForCustomer'. The conflict occurred in database 'DatabaseName', table 'Contact'.

    UDF:


    CREATE FUNCTION dbo.fnContactTypeExistForCustomer( @CustomerId INT, @ContactTypeId INT)
    RETURNS BIT AS

    BEGIN

    DECLARE @Exists BIT

    IF EXISTS
    (
    SELECT
    NULL
    FROM
    Contact
    WHERE
    CustomerId = ISNULL( @CustomerId, 0)
    AND ContactTypeId = ISNULL( @ContactTypeId, 0)
    ) SET @Exists = 1
    ELSE
    SET @Exists = 0

    RETURN @Exists
    END

  4. Adriaan New Member

    What did you do to get that error?

    Does the table already contain data?
    Does the constraint already refer to the UDF?
  5. BDRichardson New Member

    Well, I have created the UDF, and then I executed the following script:



    ALTER TABLE
    Contact
    ADD CONSTRAINT CK_Contact_PrimaryContactTypeUniqueForCustomer
    CHECK( dbo.fnContactTypeExistForCustomer( Customer, ContactTypeId) = 0)


    Yes, the data does already exist within the Contact table. However, I have checked the table, and I did not find any exceptions to the rule. Unless of course, I can only create the Constraint when the table is empty!

    I am sure the Constraint does not already exist since I have used sp_help against table and searched 'sysobjects' table to confirm.
  6. Adriaan New Member

    I'm not quite sure that you can pass parameters in a constraint definition, so - for debugging ONLY - I would add these two lines to the UDF:

    PRINT ISNULL(@CustomerId, -1)
    PRINT ISNULL(@ContactTypeId, -1)

    Then add the constraint with the NOCHECK option -

    ALTER TABLE ContactADD WITH NOCHECK
    CONSTRAINT CK_Contact_PrimaryContactTypeUniqueForCustomer
    CHECK( dbo.fnContactTypeExistForCustomer( Customer, ContactTypeId) = 0)

    ... and insert some sample data through QA. You should see the id values on the Messages tab.

    The nice thing about triggers is that you have the INSERTED snapshot, so you don't have to pass any parameters ...

    IF NOT EXISTS
    (SELECT inserted.CustomerId
    FROM inserted INNER JOIN Contact ON Contact.CustomerId = inserted.CustomerId
    WHERE Contact.ContactTypeId = 1)
    BEGIN
    RAISERROR 'blablablab'
    ROLLBACK TRANSACTION
    RETURN
    END

    and so on and so forth ...
  7. Adriaan New Member

    Okay, tested this, and you CAN pass a column as a parameter to the UDF from a constraint, it even works for batch operations -
    but you have to handle NULLs because the constraint does not fail on NULL.

    create function dbo.udf(@param int)
    returns int
    AS
    BEGIN
    Declare @r int
    set @r = case when @param is null then 0 else @param END
    return(@r)
    end
    go

    CREATE TABLE dbo.x (y int)
    go

    INSERT INTO dbo.x VALUES (null)
    INSERT INTO dbo.x VALUES (1)

    ALTER TABLE dbo.x with nocheck
    add constraint z CHECK (dbo.udf(y) <> 0)
    go

    INSERT INTO dbo.x VALUES (null)
    INSERT INTO dbo.x VALUES (1)

    DROP TABLE dbo.x
    go
    DROP function dbo.udf
    go


    ... anyway: my doubt was based on the fact that you cannot send a column as a parameter for a UDF in a default constraint.
  8. Adriaan New Member

    ... oh, but you can't use PRINT in a UDF. Stupid old me.
  9. FrankKalis Moderator

    Of course, you can use a UDF as a CHECK constraint for a column. Here's a simple example:


    CREATE FUNCTION dbo.SimpleUDFCheckConstraint(@c1 AS INT) RETURNS BIT
    AS
    BEGIN
    RETURN
    CASE
    WHEN @c1 IN (SELECT Number FROM master..spt_values WHERE Type = 'P')
    THEN 1
    ELSE 0
    END
    END
    GO
    CREATE TABLE t
    (
    c1 INT NOT NULL
    CHECK (dbo.SimpleUDFCheckConstraint(c1) = CAST(1 AS BIT))
    )

    INSERT INTO t(c1) VALUES('1')
    INSERT INTO t(c1) VALUES('-1')
    INSERT INTO t(c1) VALUES('0')

    SELECT *
    FROM t

    DROP TABLE t
    DROP FUNCTION dbo.SimpleUDFCheckConstraint

    The INSERT with -1 will fail.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs

Share This Page