SQLite DB First DDL Foreign Key Issues

[Joshua]

Hello,

I have noticed two rather strange issues regarding DB first creation of an entity model for a SQLite database.

Consider the following DDL:

CREATE TABLE [Warehouses] (
    [pk_Warehouse_ID]   integer,
    [Name]  nvarchar(254) NOT NULL,
    PRIMARY KEY ([pk_Warehouse_ID]) CONSTRAINT [pk_Warehouses]
);

CREATE TABLE [Bins] (
    [pk_Bin_ID] integer,
    [fk_Warehouse_ID]   integer,
    [Name]  nvarchar(254) NOT NULL,
    PRIMARY KEY ([pk_Bin_ID]) CONSTRAINT [pk_Bins],
    CONSTRAINT [ref_Bins_Warehouses] FOREIGN KEY ([fk_Warehouse_ID]) REFERENCES [Warehouses]([pk_Warehouse_ID])
);

The above schema works fine when clicking "Update From Database..." in the rlinq designer in Visual Studio. The foreign key relationships are created and appear in the design view as expected.

However, remove the space between "FOREIGN KEY" and "([fk_Warehouse_ID])", recreate the database, update in the designer and suddenly it loses all relationships/associations.

Good line:

CONSTRAINT [ref_Bins_Warehouses] FOREIGN KEY ([fk_Warehouse_ID]) REFERENCES [Warehouses]([pk_Warehouse_ID])

Bad line:

CONSTRAINT [ref_Bins_Warehouses] FOREIGN KEY([fk_Warehouse_ID]) REFERENCES [Warehouses]([pk_Warehouse_ID])

[Joshua]

A slightly off topic question to go along with this:

When specifying a primary key column as NOT NULL in SQLite, automatic inserts when creating a new model instance and inserting it without a primary key do not work. Once I remove the NOT NULL flag from the schema, the auto increments work as expected for the primary key.

Is this as expected?

I haven't looked in to this issue too much, but I would assume that I can still specify the column as NOT NULL and expect it to be autopopulated from the entity model instead of causing a primary key violation (since it will default to 0 and a record with that PK already exists).

[Boris Georgiev]

Hello Joshua,

Please excuse us for the inconvenience caused, it seems this is a bug on our side.
When there is no space between the FOREIGN KEY and the '(', then Telerik Data Access is not creating the association in the Domain Model. The workaround of this bug is to use only the SQL script which is generated by Telerik Data Access or to add a space between the FOREIGN KEY and the '(' bracket.

We are not aware for such an issue about using AutoInc Primary Key which is set to NOT NULL. If you are using the SQL script which you have sent in the first ticket, the Primary Key column is not set to be AutoInc. To set the Primary Key column to use the DatabaseServerCalculated Identity Mechanism, you could follow the guidance in this documentation article.

I hope that helps.

Regards,
Boris Georgiev
Telerik

 

[Joshua]

Hello Boris,

Thank you for confirming that the issue is not just user error!

Will there be a fix for this in the near future?

With regards to the AUTOINC column, we initially had it marked as such in our designer which we used in the database-first approach, but still didn't have any luck getting Data Access to recognise it as such. We used Database.NET to create our database if that's any use.

Regards

[Boris Georgiev]

Hi Joshua,

There will be a fix for this issue in one of the next releases of Telerik Data Access. Unfortunately I am not able to give you a time frame when you can expect the fix and with the direction in which the Telerik Data Access will be developed, only the Fluent Mapping will take advantage from the fix and the users who are using Visual Designer will not get the bug fix. This is why I will recommend you to setup your model using Fluent Mapping instead of XML mapping(Domain Model).

Regarding the issue with the AutoInc Primary Key, we were not able to reproduce the issue in our test environment, therefore if you could send us a copy of the database schema and the model, will help us greatly to understand the issue and assist you in the best possible way.

I am looking forward to hearing from you.

Regards,
Boris Georgiev
Telerik