Uniquekey and Tcolumnprop.unique on SQLite


The uniquekey attribute and TcolumnProp.Unique argument of the Column attribute do not work on SQLite (Aurelius 3.4.1). Is this a known issue?


No, that is not a known issue. Can you provide more details, what you mean exactly with "do not work"?

I am sorry, I will explain what I mean. I have a entity:



[Id('FOpdrachtbonId', TIdGenerator.None)]


TOpdrachtbon = class


    [Column('OpdrachtbonId', [TColumnProp.Required])]

    FOpdrachtbonId: TGuid;

    [Column('Opdrachtbonnr', [TColumnProp.Unique, TColumnProp.Required],15)]

    FOpdrachtbonnr: string;


As you can see, I tried both to use the [UniqueKey] attribute, and the TColumnProp.Unique to specify that there should be a unique index on the field Opdrachtbonnr. But when the database is updated, it does not make a (unique) index for the opdrachtbonnr field in the SQLite database. I would expect it would do that though, because the framework does not enforce this to be unique itself. So now I am able to make a TOpdrachtbon with a opdrachtbonnr that already exists.

I found out that when I create a new database it DOES create the a unique constraint. Only when I open a "old" database, that did not have this constraint yet, and update the database (using a call to TDatabasemanager.UpdateDatabase) the database is not properly altered to create this unique constraint.

I use SQLite with the FireDac connection in the latest version of Aurelius 3.4.01 and the latest version of Delphi (XE10.1 upd2). Tested it on a Win32 platform.

That's by design, see here:

The relevant part is from the "Actions" subsection until the end ("Warnings" and "Errors")

Aaah, I understand. I never knew that you could validate the schema and get more info like that. I thought updatedatabase would fail in such cases. I will implement the validate method in my app and inform the user to refetch the complete database (that will rebuild a completely new database and download it to the device).