Create foreign key constraint on JSON column

Question

JSON support in SQL Server is pretty new. See here for more information.

We would like to use this to store our translatable fields. This means in the database we would no longer have a separate table with translations but instead store them in the column itself.

Our column value would look like this:

"Name" : {
    "en-US" : "Conditioning",
    "nl-NL" : "Behandeling",
    "fr-FR" : "Traitement",
    "de-DE" : "Konditionierung"
}

Since we still have a table with all the different cultures I would still like to apply a foreign key relation between Name.Key and the Culture table.

I couldn't find any information on how to apply foreign key constraints to the data in the JSON. Does anyone have a clue how to?

I've tried the constraint below for testing but this does not work. The JSON_VALUE does not seem to work on JSON keys. Whereas JSON_QUERY returns more than just the key.

ADD CONSTRAINT
    [FK_ItemCulture]
CHECK (JSON_VALUE([Name], '$.Name') IN ('en-US', 'nl-NL', 'fr-FR', 'de-DE'))

Answer 1

You can define a scalar function that will check a single json:

CREATE FUNCTION [dbo].[svf_CheckJsonNames](@json nvarchar(max))
RETURNS bit AS
BEGIN
    declare @ok bit
    declare @validNames table([name] nvarchar(50))

    insert into @validNames values ('en-US'),('nl-NL'),('fr-FR'),('de-DE')

    if exists (
            select x.[key] , vn.name  from OPENJSON (@json)  
            with (
                [Name] nvarchar(max) as json
            ) t
            cross apply 
            (
            select [key] from openjson(t.[Name])
            ) x
            left join  @validNames vn
            on x.[key] COLLATE DATABASE_DEFAULT = vn.[name] COLLATE DATABASE_DEFAULT
            where vn.[name] is null
        )
        set @ok = 0
    else set @ok = 1

    RETURN @ok
END

The functions returns 1 if all names are valid, 0 if one or more names are invalid.

Now you can use this function in your constraint:

create table tmp(myId int, [Name] nvarchar(max))

alter table tmp ADD CONSTRAINT [FK_ItemCulture]
    CHECK ([dbo].[svf_CheckJsonNames]([Name]) = 1)  

if you run the following insert statements:

insert into tmp values(1, '{"Name" : { "en-US" : "Conditioning", "nl-NL" : "Behandeling", "fr-FR" : "Traitement", "de-DE" : "Konditionierung" }}')
insert into tmp values(2, '{"Name" : { "en-EN" : "Conditioning", "nl-NL" : "Behandeling", "fr-FR" : "Traitement", "de-DE" : "Konditionierung" }}')

the first will succed because all names are correct:

but the second will fail since the first name ("en-EN") is invalid. Here is the error: