Enforcing Foreign Key Constraint In A Multi-Valued Column In SQL Server

I have seen that sometimes, a few developers create a multi-valued column to store more than one value in a comma-separated manner (like 1,3,4) and then, they read the individual values by splitting using comma.

However, due to such design, they can't add a foreign key constraint like below.

ALTER TABLE <Table-name>
ADD CONSTRAINT <FK-Name> FOREIGN KEY (<col-name>) REFERENCES <Lookup Table-name>(<Lookup col-name>);

PS - Personally, I am not a fan of such design and I would recommend having a mapping table in such cases; however, at times, mostly on the existing system, you don't have the choice to rewrite or change the design and hence finding a quick fix is the only option.

To illustrate the problem and solution, let's take an example of two tables - Employee and Country - as below.

CREATE TABLE Country (
Id INT NOT NULL PRIMARY KEY,
Name varchar(100) NOT NULL,
Code varchar(50) NULL
);
CREATE TABLE Employee (
Id INT NOT NULL PRIMARY KEY,
HomeCountryId INT NOT NULL,
VisitedCountryIds varchar(200) NULL,
Constraint FK_Employee_Country FOREIGN KEY (HomeCountryId) REFERENCES Country(Id)
);

Let's assume the country id as 1, 2 till 249 (As per the latest data available during the time of writing the post).

As you can see there is FK constraint on the HomeCountryId, hence only valid Country Id (from 1-249) can be entered; however, in the field VisitedCountryIds, there is no check and any id (like 250, 251, etc.) can also be added even if it doesn't exist in the country table. Well, this can lead to the data integrity issue.

So how we can make sure that users can only enter valid country ids (from 1-249) in the VisitedCountryIds column?

The fix is two-fold as following.

Create the function in the SQL Server as below.

CREATE FUNCTION [dbo].[svf_CheckCountryIds](@CountryIds nvarchar(200))
RETURNS bit AS
BEGIN
declare @valid bit
declare @rowsInserted INT
declare @addedCountryIds table([CountryId] nvarchar(200))
insert into @addedCountryIds
select value from STRING_SPLIT(@CountryIds, ',')
set @rowsInserted = @@rowcount
if (@rowsInserted = (select count(a.CountryId) from @addedCountryIds a join [Country] b on a.CountryId = b.Id))
begin
set @valid = 1
end
else
begin
set @valid = 0
end
RETURN @valid
END

As you can see in the above function, we are passing the column data that is in the comma concatenated form and then they are split using STRING_SPLIT function and stored in the addedCountryIds table variable. Also, the inserted row count is stored in the rowsInserted variable.

Later, the values on addedCountryIds arejoined with Country table and if the count is matching, i.e., if all the passed country id is present in the Country table, true/1 is returned else false/0 is returned.

Create the FK with check constraint on the VisitedCountryIds as follows,

ALTER TABLE Employee
ADD CONSTRAINT [FK_Employee_VisitedCountryIds] CHECK ([dbo].[svf_CheckCountryIds]([VisitedCountryIds]) = 1)

As you can see constraint FK_Employee_VisitedCountryIds is created on VisitedCountryIds with condition that function svf_CheckCountryIds should return value as 1/true.

Now when you enter any country id other than 1 to 249, for example, if you enter VisitedCountryIds as '103,236,250', an error will be thrown as follows as id 250 is not the part of the country id list.

Msg 547, Level 16, State 0, Line 4

The INSERT statement conflicted with the CHECK constraint "FK_Employee_VisitedCountryIds". The conflict occurred in database "TestDb", table "dbo.Employee", column 'VisitedCountryIds'.

The statement has been terminated.

However, if you enter VisitedCountryIds as '103,236,249', it will be successfully inserted because all the ids are part of the country list.

I hope you found this post useful in handling the foreign keys in multivalued columns. Looking forward to your comments.