RFqf4d4p

1Sql constraint method returns a false value
2CREATE TABLE [relations].[CompoundKey_Contacts](
3    [compoundId] [varchar](32) NOT NULL,
4    [companyId] [varchar](32) NULL,
5    [personId] [varchar](32) NULL,
6    [contactInfoId] [varchar](32) NOT NULL)
7	
8ALTER TABLE [relations].[CompoundKey_Contacts]  WITH NOCHECK ADD  CONSTRAINT [CK_CompoundKey_Contacts] CHECK  (([relations].[doesThisCompoundKeyExist]([personId],[companyId])='NO'))
9GO
10
11ALTER TABLE [relations].[CompoundKey_Contacts] CHECK CONSTRAINT [CK_CompoundKey_Contacts]
12GO
13	
14CREATE function [relations].[doesThisCompoundKeyExist](
15    @personId varchar(32),
16    @companyId varchar(32)
17)
18returns varchar(3)
19as
20begin
21    declare @exists varchar(32)
22
23    if(@companyId is null and @personId is null)
24      set @exists = 'YES'
25    else if(@personId is null)
26        if exists(select compoundId from relations.CompoundKey_Contacts where personId is null AND companyId = @companyId)
27            set @exists = 'YES' 'This is where to code enters, but it should come to the else and return 'NO'
28        else
29            set @exists = 'NO'
30    else if(@companyId is null)
31        if exists(select compoundId from relations.CompoundKey_Contacts where personId = @personId AND companyId is null)
32            set @exists = 'YES'
33        else
34            set @exists = 'NO'
35    else if exists(
36        select compoundId from relations.CompoundKey_Contacts where personId = @personId AND companyId = @companyId
37        )
38        set @exists = 'YES'
39    else
40        set @exists = 'NO'
41    return @exists
42end;
43	
44insert into relations.CompoundKey_Contacts (companyId, contactInfoId, personId, compoundId) values ('COM-000015945', 'INF-000144406', null, 'CPK-000000067');
45	
46if exists(select compoundId from relations.CompoundKey_Contacts where personId is null AND companyId = 'COM-000015945')
47                print 'YES'
48            else
49                print 'NO' 'Returns NO as it should.
50	
51The INSERT statement conflicted with the CHECK constraint "CK_CompoundKey_Contacts". The conflict occurred in database "domas", table "relations.CompoundKey_Contacts".
52The statement has been terminated.
53	
54ALTER TABLE CompoundKey_Contacts
55    ADD CompoundKey AS ISNULL(personID, 'NOPERSONID') + ISNULL(companyId, 'NOCOMPANYID');
56ALTER TABLE CompoundKey_Contacts WITH CHECK
57    ADD CONSTRAINT UQ_CompoundKey_Contacts_CompoundKey UNIQUE (CompoundKey);
58	
59ALTER TABLE CompoundKey_Contacts WITH CHECK
60    ADD CONSTRAINT UQ_CompoundKey_OtherUnique UNIQUE (personID, companyId);