How do I refresh data from the database if there are missing primary keys in the existing data?

Posts   
 
    
JMitchell avatar
JMitchell
User
Posts: 128
Joined: 01-Sep-2006
# Posted on: 03-Sep-2010 12:33:06   

I have migrated a 2.6 project. I tried to generate the code but it seems tables missing primary keys now give errors and stop you generating the code.

Fair enough, I've removed two un-necessary tables and added primary keys to the others.

When I right click on SQL Server (SqlClient) through either Relational Model Data in the Project Explorer or via the Catalog Explorer and click Refresh Relational Model Data from a Database, it gives me the same errors about the missing primary keys I have created in the database.

How should I proceed?

JMitchell avatar
JMitchell
User
Posts: 128
Joined: 01-Sep-2006
# Posted on: 03-Sep-2010 14:05:27   

I removed the problem tables and added them back in afterwards. I still think this needs to be looked at though.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 03-Sep-2010 18:02:47   

JMitchell wrote:

I removed the problem tables and added them back in afterwards. I still think this needs to be looked at though.

Every entity mapped onto a table requires to have 1 or more identifying fields. In v2.6 we didn't force that upon users, in v3 we do.

After a converted project you might fall into the trap that you can't refresh. So do the following: - for the entities which give you this error, select 1 field and mark it as the identifying field. YOu can do that in project explorer, by right-clicking the field, or by opening the entity in the entity editor. - click 'validate and adjust relational model data'. This will set the target field in the table information as PK.

You now have two options: 1) export the DDL SQL and apply it to the schema in your database. (recommended). This thus makes the table have a pk. You can now refresh OK. or 2) set in project properties the setting 'IdentifyingFieldsFollowDBPrimaryKeyConstraints' to false. When you now refresh you'll get a dialog which shows you 3 options: export, abort or ignore the export. Click this last option. Then refresh normally.

The second option has the problem that you will have to manually assign PK fields. So it's recommended to have each table in your db to have a PK.

If you use these tables as readonly tables anyway, it might be a good idea to map a typedview on them instead of an entity (new in v3). The code you use in your project is a little different, but it doesn't force a PK on you, and as you use the data in a read-only fashion anyway, it's perhaps more fitting your domain.

Frans Bouma | Lead developer LLBLGen Pro
Pablo
User
Posts: 81
Joined: 21-Mar-2005
# Posted on: 12-Dec-2013 14:41:51   

Frans, I appologize for being critic here. I'm a database first kind-a-guy ...

When a problem is detected in the database, which prevents proper generation, then the expected sequence is to fix it in the database and refresh the datamodel.

That ought to fix it right???

Paul

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 13-Dec-2013 10:06:20   

Pablo wrote:

Frans, I appologize for being critic here. I'm a database first kind-a-guy ...

When a problem is detected in the database, which prevents proper generation, then the expected sequence is to fix it in the database and refresh the datamodel.

That ought to fix it right???

That would make it not detect the problem (if I understand you correctly) indeed.

Frans Bouma | Lead developer LLBLGen Pro
Pablo
User
Posts: 81
Joined: 21-Mar-2005
# Posted on: 14-Dec-2013 20:28:04   

Otis, The problem is that it does not.

When i ran into this problem, it turned out a rather frustrating experience, namely .. fixing it in the database dit not do anything to enable llblgen designer to refresh the model from the database...

My first solution was to delete all entities from the project and refresh again etc. My current solution is to fix the problem twice ... once in the databas, then in the entity in the designer ... and than i'm able to refresh the model again from the database.

Now, being aware of a 'working' workaround, it is ok ... it still feels rather stupid though. If you could fix it, I'd appreciate it very much (I'm sure others will be too)!

Paul

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 16-Dec-2013 10:58:46   

What's the specific problem you ran into? I can't fix things I don't know the specific details of simple_smile

The general idea is that if the db has a problem, it's not OK to refresh the project as that might have devastating results for the project which you then have to correct manually. So we opt for not refreshing when you run into a problem.

Frans Bouma | Lead developer LLBLGen Pro
Pablo
User
Posts: 81
Joined: 21-Mar-2005
# Posted on: 16-Dec-2013 17:33:27   

Which problem to solve ... fair enough! I'll open a new issue.