CODEWITHSUNDEEP
sqlsql-serverfunctiont-sqlsql-server-2012
Marthin
Marthin

Reputation: 6543

Sql constraint method returns a false value

I have a problem with a constraint method in sql.

This is my table

CREATE TABLE [relations].[CompoundKey_Contacts](
    [compoundId] [varchar](32) NOT NULL,
    [companyId] [varchar](32) NULL,
    [personId] [varchar](32) NULL,
    [contactInfoId] [varchar](32) NOT NULL)

When you add a row to this table it should check that this combination of person and company does not already exist in the table. For this I use a constraint function

Constraint

ALTER TABLE [relations].[CompoundKey_Contacts]  WITH NOCHECK ADD  CONSTRAINT [CK_CompoundKey_Contacts] CHECK  (([relations].[doesThisCompoundKeyExist]([personId],[companyId])='NO'))
GO

ALTER TABLE [relations].[CompoundKey_Contacts] CHECK CONSTRAINT [CK_CompoundKey_Contacts]
GO

Function

CREATE function [relations].[doesThisCompoundKeyExist](
    @personId varchar(32),
    @companyId varchar(32)
)
returns varchar(3)
as
begin
    declare @exists varchar(32)

    if(@companyId is null and @personId is null)
      set @exists = 'YES'
    else if(@personId is null)
        if exists(select compoundId from relations.CompoundKey_Contacts where personId is null AND companyId = @companyId)
            set @exists = 'YES' 'This is where to code enters, but it should come to the else and return 'NO'
        else
            set @exists = 'NO'
    else if(@companyId is null)
        if exists(select compoundId from relations.CompoundKey_Contacts where personId = @personId AND companyId is null)
            set @exists = 'YES'
        else
            set @exists = 'NO'
    else if exists(
        select compoundId from relations.CompoundKey_Contacts where personId = @personId AND companyId = @companyId
        )
        set @exists = 'YES'
    else
        set @exists = 'NO'
    return @exists
end;

My insert statement that fails

insert into relations.CompoundKey_Contacts (companyId, contactInfoId, personId, compoundId) values ('COM-000015945', 'INF-000144406', null, 'CPK-000000067');

The problem is this. When I run an insert on the table with a unique insert it still fails. I have of course checked that it rely is unique with a select statement. And here comes the funny part. When I do debug it and check where it fails and break out that code part and run it free without being in a function it behaves as it should so the following code works if its not run in a function

if exists(select compoundId from relations.CompoundKey_Contacts where personId is null AND companyId = 'COM-000015945')
                print 'YES'
            else
                print 'NO' 'Returns NO as it should.

This is the error msg I get

The INSERT statement conflicted with the CHECK constraint "CK_CompoundKey_Contacts". The conflict occurred in database "domas", table "relations.CompoundKey_Contacts".
The statement has been terminated.

I run this on both a Sql Server 2012 and Sql Server 'DENALI' CTP3

Upvotes: 0

Views: 213

Answers (2)

gbn
gbn

Reputation: 432421

Using a UDF in a check constraint won't work reliably as you can see

Use a unique constraint on a computed column if you require extra logic

ALTER TABLE CompoundKey_Contacts
    ADD CompoundKey AS ISNULL(personID, 'NOPERSONID') + ISNULL(companyId, 'NOCOMPANYID');
ALTER TABLE CompoundKey_Contacts WITH CHECK
    ADD CONSTRAINT UQ_CompoundKey_Contacts_CompoundKey UNIQUE (CompoundKey);

Or a simple unique constraint

ALTER TABLE CompoundKey_Contacts WITH CHECK
    ADD CONSTRAINT UQ_CompoundKey_OtherUnique UNIQUE (personID, companyId);

Upvotes: 4

Martin Smith
Martin Smith

Reputation: 453608

Create a unique constraint or unique index on personId,companyId.

Don't try using a check constraint with a UDF for this as it is inefficient and difficult to get correct anyway.

Upvotes: 2

Related Questions