I have an issue with adding a NULL value in a Foreign Key column. I have set that the foreign key to be NULLABLE, also I have enabled using foreign keys in C#.
This is my C# code:
private static SQLiteConnection _conn = new SQLiteConnection("DataSource = D:/SQLDB.sqlite;foreign keys=true;");
string sql = @"INSERT INTO Scan(id, name, employeeId, scannerId, orderItemId) VALUES (1,'name',1,1, NULL);";
SQLiteCommand cmd = new SQLiteCommand(sql, _conn);
cmd.ExecuteNonQuery()
I have a table Scan, which has 3 foreign keys employeeId, scannerId and orderItemId. EmployeeId and ScannerId can not be null, but orderItemId can, because it won't be available until the scan status is finished.
The above SQL INSERT cmd throws an exception:
foreign key mismatch - "Scan" referencing "OrderItem"
I don't understand why, because when I execute the same command in SQLite manually it works fine.
This is how I created Scan table in SQLite:
CREATE TABLE `Scan` (
`id` INTEGER NOT NULL,
`name` VARCHAR(255),
`employeeId` INTEGER NOT NULL,
`scannerId` INTEGER NOT NULL,
`orderItemId` INTEGER,
PRIMARY KEY(id),
FOREIGN KEY(`employeeId`) REFERENCES `Employee`(`id`),
FOREIGN KEY(`scannerId`) REFERENCES `Scanner`(`id`),
FOREIGN KEY(`orderItemId`) REFERENCES `OrderItem`(`id`)
);
Can you tell me where my mistake/mistakes are? What am I doing wrong?