0

This question already has an answer here:

Here's a simplified view of my database

|---------------|     |-----------|
| ORGANIZATION  |     | CONTACT   |
|---------------|     |-----------|
| OrgId         |     | ContactId |
| OrgName       |     | OrgId     |
| MainContactId |     | FullName  |
|---------------|     |-----------|

Is there a way to ensure that MainContactId only belongs to the subset of the CONTACT table where the OrgId matches?

I did some looking and found that I can use a user defined function, but that appears to have performance issues. Do I have any other options here?

EDIT: I played around, and got the following working. Is there a problem with this approach?

    CREATE FUNCTION dbo.CheckOrgMainContact(@OrgId int, @ContactId int)
    RETURNS int
    AS
    BEGIN
        DECLARE @Result int

        IF @ContactId IS NULL --if the contact ID is not set, don't check it
        BEGIN
           SET @Result = 1
        END
        ELSE
        BEGIN
            IF EXISTS (SELECT * 
                       FROM dbo.Contact
                       WHERE OrgId = @OrgId and 
                             ContactId = @ContactId)
               SET @Result = 1
            ELSE
               SET @Result = 0
        END
        RETURN @Result
    END
    GO

    CREATE TABLE dbo.Organization
    (
       OrgId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
       OrgName varchar(50) NOT NULL,
       MainContactId int NULL,
       CONSTRAINT [CheckOrgMainContact_Constraint] CHECK (dbo.CheckOrgMainContact(OrgId, MainContactId)=1)
    )
    GO

    CREATE TABLE dbo.Contact
    (
       ContactId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
       OrgId int NOT NULL FOREIGN KEY REFERENCES dbo.Organization(OrgId),
       FullName varchar(50) NOT NULL
    )  
    GO
    ALTER TABLE Organization 
    ADD CONSTRAINT FK_MainContact FOREIGN KEY (MainContactId) 
        REFERENCES dbo.Contact (ContactId) 
    GO

    --let's get some date inot the tables
    SET IDENTITY_INSERT Organization ON
    INSERT INTO Organization (OrgId, OrgName)
    VALUES(1, 'Google'),
          (2, 'Microsoft'),
          (3, 'Leidos');
    SET IDENTITY_INSERT Organization OFF

    SET IDENTITY_INSERT Contact ON
    INSERT INTO Contact (ContactId, OrgId, FullName)
    VALUES (1, 1, 'Homer'),
           (2, 1, 'Marge'),
           (3, 1, 'Bart'),
           (4, 2, 'Bob'),
           (5, 2, 'Linda'),
           (6, 2, 'Gene'),
           (7, 3, 'Frank'),
           (8, 3, 'Dee'),
           (9, 3, 'Dennis')
    SET IDENTITY_INSERT Contact OFF


    UPDATE Organization SET MainContactId = 1 WHERE OrgName = 'Google' --set this to a valid contact

    UPDATE Organization SET MainContactId = 4 WHERE OrgName = 'Google' --try setting this to an invalid contact


    SELECT o.OrgName, c.FullName FROM Organization o INNER JOIN Contact c on o.OrgId = c.OrgId --get all the contacts
    SELECT o.OrgName, c.FullName as MainContact FROM Organization o LEFT JOIN Contact c ON o.MainContactId = c.ContactId

marked as duplicate by ypercubeᵀᴹ, RolandoMySQLDBA, Philᵀᴹ, Paul White, dezso Jan 29 '15 at 6:03

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.