The INSERT statement conflicted with the FOREIGN KEY constraint “FK__…”

I’m a very new beginner with SQL and I’m battling a problem I can’t seem to find the answer to. I’ve found similar questions here on Stack Overflow but I still can’t see what I’m supposed to change in my code to make it work.

I’ve got 4 tables in a relation as follows:

CREATE TABLE DeviceLocation (
    LocationId int not null identity(1,1) primary key,
    Latitude nvarchar(50) not null,
    Longitude nvarchar(50) not null
)

GO
CREATE TABLE TempHumidSensor (
    TempHumidSensorId int not null identity(1,1) primary key,   
    Humidity float not null,
    Temperature float not null
)

GO
CREATE TABLE LightSensor (
    LightSensorId int not null identity(1,1) primary key,
    LightPercentage int not null
)

GO
CREATE TABLE DeviceMessage (
    MessageId nvarchar(50) not null primary key,
    DeviceId nvarchar(20) not null,

    temperatureAlert nvarchar(20) not null,
    institutionClassName nvarchar(20) not null,
    institutionName nvarchar(20) not null,
    developerName nvarchar(20) not null,

    MessageTimestamp nvarchar(20) not null,
    TempSensorId int not null references TempHumidSensor(TempHumidSensorId),
    LightSensorId int not null references LightSensor(LightSensorId),
    DeviceLocationId int not null references DeviceLocation(LocationId)
)
GO

Then, I’ve got three procedures that are returning the identity value primary key for DeviceLocation, TempHumidSensor, LightSensor before inserting in to DeviceMessage, as follows:

GetDeviceLocation

ALTER procedure [dbo].[GetDeviceLocation]
    @Latitude nvarchar(50), 
    @Longitude nvarchar(50)
as
begin
    if not exists (select 1 from DeviceLocation where Latitude = @Latitude and Longitude = @Longitude)
        insert into DeviceLocation output inserted.LocationId values (@Latitude, @Longitude)
    else
        select LocationId from DeviceLocation where Latitude = @Latitude and Longitude = @Longitude
end

GetLightSensor

ALTER procedure [dbo].[GetLightSensor]
    @LightPercentage int
as
begin
    if not exists (select 1 from LightSensor where LightPercentage = @LightPercentage)
        insert into LightSensor output inserted.LightSensorId values (@LightPercentage)
    else
        select LightSensorId from LightSensor where LightPercentage = @LightPercentage
end

GetTempHumidSensor

ALTER procedure [dbo].[GetTempHumidSensor]
    @Humidity float, 
    @Temperature float
as
begin
    if not exists (select 1 from TempHumidSensor where Humidity = @Humidity and Temperature = @Temperature)
        insert into TempHumidSensor output inserted.TempHumidSensorId values (@Humidity, @Temperature)
    else
        select TempHumidSensorId from TempHumidSensor where Humidity = @Humidity and Temperature = @Temperature
end

Finally, there’s the SaveSqlData procedure that is called upon by .NET code in the end.

ALTER PROCEDURE [dbo].[SaveSqlData]
    @Latitude NVARCHAR(50),
    @Longitude NVARCHAR(50),
    @Humidity FLOAT,
    @Temperature FLOAT,
    @LightPercentage INT,
    @MessageTimestamp NVARCHAR(20),
    @MessageId NVARCHAR(50),
    @DeviceId NVARCHAR(20),
    @temperatureAlert NVARCHAR(20),
    @institutionClassName NVARCHAR(20),
    @institutionName NVARCHAR(20),
    @developerName NVARCHAR(20)

AS
BEGIN
    DECLARE @DeviceLocationId INT,
            @TempHumidSensorId INT,
            @LightSensorId INT

    EXEC @TempHumidSensorId = GetTempHumidSensor @Humidity = @Humidity, @Temperature = @Temperature
    EXEC @LightSensorId = GetLightSensor @LightPercentage = @LightPercentage
    EXEC @DeviceLocationId = GetDeviceLocation @Latitude = @Latitude, @Longitude = @Longitude


    INSERT INTO dbo.DeviceMessage(
        MessageId, 
        DeviceId, 
        temperatureAlert, 
        institutionClassName, 
        institutionName,
        developerName,
        MessageTimestamp,
        TempHumidSensorId,
        DeviceLocationId,
        LightSensorId
    ) 
    VALUES (
        @MessageId, 
        @DeviceId, 
        @temperatureAlert, 
        @institutionClassName, 
        @institutionName,
        @developerName,
        @MessageTimestamp,
        @TempHumidSensorId,
        @DeviceLocationId,
        @LightSensorId
    )
    END

I usually test this procedure with this snippet:

exec dbo.SaveSqlData
    @Latitude = '55.5555',
    @Longitude = '66.6666',
    @Humidity = 41.2,
    @Temperature = 25.0,
    @LightPercentage = 91,
    @MessageTimestamp = 123123123,
    @temperatureAlert = 'false',
    @MessageId = 'sd344d3j-guid-mock-124-123',
    @DeviceId = 'device-name-from-device',
    @institutionClassName = 'class',
    @developerName = 'test',
    @institutionName = 'institution'

The error that shows both when this method is invoked from C# and by the test call above says:

(1 row affected)

(1 row affected)

(1 row affected)
Msg 547, Level 16, State 0, Procedure dbo.SaveSqlData, Line 27 [Batch Start Line 0]
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__DeviceMes__TempH__11D4A34F". The conflict occurred in database "telemetry-sqldb", table "dbo.TempHumidSensor", column 'TempHumidSensorId'.
The statement has been terminated.

I’ve read that it has to do with the order of inserts, but I’m not sure how to fix it or where the problem lies exactly.

I can see that there are inserts to all tables except for DeviceMessage, when this error occures. Any help is vastly appreciated.

Answer

You can see the problem by putting a diagnostic SELECT @DeviceLocationId etc in SaveSqlData – you will get zeroes.

The way you’re getting the Ids back from your Get sprocs doesn’t work. Each sproc SELECTs the id, but the caller (the EXEC in SaveSqlData) expects a return value.

Change your Get sprocs to RETURN the id, eg:

ALTER procedure [dbo].[GetTempHumidSensor]
    @Humidity float, 
    @Temperature float
as
begin
    declare @id int

    if not exists (select 1 from TempHumidSensor where Humidity = @Humidity and Temperature = @Temperature)
        insert into TempHumidSensor values (@Humidity, @Temperature)

    select @id = TempHumidSensorId from TempHumidSensor where Humidity = @Humidity and Temperature = @Temperature

    return @id
end

Note you also have a typo in SaveSqlData, the column in DeviceMessage is named TempSensorId not TempHumidSensorId.

Advertisement

Answer