Sqlite foreign key insert should fail

Question

Why is Sqlite allowing me to insert a foreign key in table2 that does not match with any primary keys in table1? I am using .NET and System.Data.SQLite.dll

Here is my setup:

create table [table1] (
  [Id] integer primary key NOT NULL
, [col1] nvarchar(100)  NULL
);

create table [table2] (
  [Id] integer primary key NOT NULL
, [col2] integer  NOT NULL
, FOREIGN KEY(col2) REFERENCES table1(Id)
);

INSERT INTO [table1] ([col1]) VALUES ('val1');
INSERT INTO [table2] ([col2]) VALUES ('2');

SQL fiddle: http://sqlfiddle.com/#!7/4fccc/1/0

Seems like a duplication, although i solved it with enabling the foreign key constraints in the connection string like so: "Data Source=:memory:;foreign keys=true;" — rlee, May 22 '16 at 15:40

score 1 · Accepted Answer · answered May 22 '16 at 15:44

1

Enable the foreign key constraints in the connection string like so: "Data Source=:memory:;foreign keys=true;"

answered May 22 '16 at 15:44

rlee

293
1
8

Sqlite foreign key insert should fail

1 Answers1