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
