0

I was trying to delete a row from a table where no reference is at place and got "foreign key missmatch" . I.e. i got a table which values say a,b references another table's values, let's say a1,b1. I am trying to delete the values a,b which refer to some other values but not refered at.

>Create Table Sensor(
SensorId INT,
RoomId int NOT NULL,
MoistureMeasurementPrecision DOUBLE,
HeatMeasurementPrecision double,
MaximumBatteryLife double,
Manufacturer varchar(20),
Primary key(SensorId,RoomId),
Foreign key(RoomId) references Room(RoomId),
Check (MoistureMeasurementPrecision > 0 & MoistureMeasurementPrecision *<=100),
Check (HeatMeasurementPrecision > 0 & HeatMeasurementPrecision <=100),
Check (MaximumBatteryLife>0));

Create Table Measurement(
TimeofMeasurement TIME,
RoomId INT NOT NULL,
SensorId INT NOT NULL,
Heat DOUBLE,
Moisture DOUBLE,
primary key(RoomId,SensorId,TimeofMeasurement),
foreign key(RoomId) references Sensor(RoomId),
foreign key(SensorId) references Sensor(SensorId),
check(Moisture>0));

sqlite> delete from Measurement where RoomId= 1;

Error: foreign key mismatch - "Measurement" referencing "Sensor"

I do not provide the contents of the tables as my problem is that there is no reference to that table. enter image description here

Tried the same thing on sqlite studio and got this error:[21:53:30] Error while executing SQL query on database 'First Database': foreign key mismatch - "Measurement" referencing "Sensor".

Kostas Thanasis
  • 368
  • 4
  • 11
  • 1
    Check this: https://stackoverflow.com/a/5208331/10498828 – forpas Nov 11 '19 at 17:17
  • Aside from the error, do you mean to setup a **multiple-column foreign key relationship** between Measurement and Sensor tables? That is different than defining two separate foreign-key relationships on separate columns. – C Perkins Nov 11 '19 at 18:58
  • I wanted to add them (both columns) as one foreign key referencing to two columns of the sensor. Only one of which is a primary key. Do not know if that is relevant or not. I also encountered the same problem on sqlite studio. Trying to delete,that is, the row from the measurement while it has no reference to it. Getting the same error: [21:02:53] Error while deleting row from table Measurement: foreign key mismatch - "Measurement" referencing "Sensor" – Kostas Thanasis Nov 11 '19 at 19:06

0 Answers0