SQLite allows a primary when autoincrement is specified

Question

I am using SQLite to test my DAO project. The reason is it provides a quick way to tell me if the model can perform a CRUD operation.

So there is an NUNIT project that tests each DAO and indicates failures.

However I ran into an issue where a Class that has the Primary marked as assigned and the table has autoincrement turned on, the insert does not fail as it should in SQL Server.

Here is the syntax I used to created the SQLite table in memory.

CREATE TABLE dbo.Control ([ControlNumber] INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL ,[ControlDescription] TEXT    NULL ,[ControlNumeric] REAL    NULL ,[ControlText] TEXT    NULL ,[ControlGroupKey] INTEGER    NULL )

How can I get the insert to fail indicating there is an issue regarding the modelling?

Answer 1

SQLite is a lot more forgiving than other databases when it comes to storing data. The only thing I can think of to make this fail is to create an INSERT trigger that somehow makes the query fail.

To my knowledge though DAO does not dictate how a database should react when a value is provided for an autoincrement field, which would make the test project too restrictive (or SQL Server specific).