IAlterPoco.Insert fails to raise Foreign Key Violation

[albertjleo]

Using SQLite provider, performing an Insert operation does NOT fail or raise exception if a Foreign Key Constraint is violated, but inserts the record successfully leaving the table in an incorrect state for future operations.

This can be reproduced using PetaPoco.Tests.Integration\BaseInsertTests.cs

The FK_Orders_People when executing from SQLiteBrowser or sqlite runtime throws an exception if an invalid (non existent) PersonId is specified when attempting to Insert an Order record.

CREATE TABLE [People] (
	[Id] TEXT NOT NULL PRIMARY KEY,
	[FullName] TEXT,
	[Age] INTEGER NOT NULL,
	[Height] INTEGER NOT NULL,
	[Dob] INTEGER NULL
);

CREATE TABLE [Orders] (
	[Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	[PersonId] TEXT NOT NULL,
	[PoNumber] TEXT NOT NULL,
	[OrderStatus] INTEGER NOT NULL,
	[CreatedOn] INTEGER NOT NULL,
	[CreatedBy] TEXT NOT NULL,
	CONSTRAINT [FK_Orders_People] FOREIGN KEY ([PersonId]) REFERENCES [People]([Id])
);

However this exception is not raised by PetaPoco which leaves the database table in an incorrect state.

To produce the issue, comment out DB.Insert(_person) and _order.PersonId = _person.Id; (as shown below) and executing DB.Insert(_order); works fine returning the Order.Id

        [Fact]
        public void Insert_WhenInsertingRelatedPocosAndGivenPoco_ShouldInsertPocos()
        {
            //DB.Insert(_person);
            //_order.PersonId = _person.Id;
            DB.Insert(_order);
            _orderLine.OrderId = _order.Id;
            DB.Insert(_orderLine);

            var personOther = DB.Single<Person>(_person.Id);
            var orderOther = DB.Single<Order>(_order.Id);
            var orderLineOther = DB.Single<OrderLine>(_orderLine.Id);

            personOther.ShouldNotBeNull();
            personOther.ShouldBe(_person);
            orderOther.ShouldNotBeNull();
            orderOther.ShouldBe(_order);
            orderLineOther.ShouldNotBeNull();
            orderLineOther.ShouldBe(_orderLine);
        }

• Tried forcing ForeignKey Pragma before this operation by inserting statement: DB.Execute("PRAGMA foreign_keys=1"); before DB.Insert(_order); but this does not change the behaviour.
• Subscribed to IDatabase.ExceptionThrown to log exception, but none were raised.
• Presumably IDbCommand.ExecuteScalar in Database.ExecuteScalarHelper should be throwing an exception on Foreign key violation, but it isn't.
• Not sure if this a configuration issue, so would appreciate a workaround or fix, since relying on Foreign key violations to work.

[pleb]

The schema and test don't match. Can you post an update without commented out code and a schema which matches the test?

[albertjleo]

The Schema is PetaPoco.Tests.Integration/Scripts/SqliteBuildDatabase.sql

The original Test is Insert_WhenInsertingRelatedPocosAndGivenPoco_ShouldInsertPocos() in PetaPoco.Tests.Integration/Databases/BaseInsertTests.cs

The Constraint CONSTRAINT [FK_Orders_People] FOREIGN KEY ([PersonId]) REFERENCES [People]([Id]) should fail (throw an exception) if People.Id is NOT specified when Inserting into Orders DB.Insert(_order);, however it does not as you'd expect if you remove the insert into People

DB.Insert(_person);
_order.PersonId = _person.Id;

from the original test as shown below

[Fact]
        public void Insert_WhenInsertingRelatedPocosAndGivenPoco_ShouldInsertPocos()
        {
            DB.Insert(_order);
            _orderLine.OrderId = _order.Id;
            DB.Insert(_orderLine);

            var personOther = DB.Single<Person>(_person.Id);
            var orderOther = DB.Single<Order>(_order.Id);
            var orderLineOther = DB.Single<OrderLine>(_orderLine.Id);

            personOther.ShouldNotBeNull();
            personOther.ShouldBe(_person);
            orderOther.ShouldNotBeNull();
            orderOther.ShouldBe(_order);
            orderLineOther.ShouldNotBeNull();
            orderLineOther.ShouldBe(_orderLine);
        }

[pleb]

Ah I see. Unless there's an issue with how I've done the Sqlite DDL, this is a Sqlite thing. PetaPoco simply extends ado.net and adds a few helper features.