Question: Error Message on Tables with No Primary Key

Sep 20, 2007 at 7:48 PM
I am using the framework to quicken development against an existing database.

I have several tables which do not have a primary key (and cannot be modified to contain an artificial key at this time). When I generate code using version 10781 (and earlier versions), I get the following error message:

"Table "MyExampleTable" does not have a primary key. Please use the HasPrimaryKey property to check for the existance of a primary key before accessing the PrimaryKey property."

However, the error message does not indicate where this check should be, etc etc -

At this time, can the framework be used to access / model tables which have no primary key?


For now, I can "exclude tables", as the main table I'm accessing has a primary key, but this is somewhat kludgy and incomplete. I can certainly imagine a day soon when I would want to have all of the tables in the DB accessed via objects in the same manner.

PS - thanks for the extensive work on the Framework thus far.
Coordinator
Sep 21, 2007 at 12:26 AM
The way it's written currently, we do require tables to have primary keys because of the way we handle gets, updates and deletes. Without a key to uniquely identify a particular record, we can't generate those methods. How do you identify individual records, or can you explain a little more what the table is for?
Sep 21, 2007 at 1:56 PM
Edited Sep 21, 2007 at 1:57 PM
Typically, these tables are lookup tables, generated directly from imported data on another source (say, a view in a Oracle DB).

The database is quite old, having been created in SQL Server 6.5 and migrated upward with no modification. Heaven only knows why in the world it was built this way w/o artificial keys... or why cascading inserts are still handled through... oh, you don't want to know.

ANYWAY.

An acceptable alternative would be to check the table for a primary key, and if none is found, generate a SQL script which is named SKIPPEDTABLE-TABLENAME.SQL or something like that. That would just be a way of automating my workaround above. At very least, having the check in place would prevent crashing the rest of the auto-generation.

Theoretically, you could also generate incomplete objects, with some marker indicating that GetAll and Save are the only available methods (or something...).
Coordinator
Sep 23, 2007 at 3:16 AM
We are discussing what the best way to fix this issue is. We have a few options, and will hopefully make a decision soon. We are pushing towards an initial release at the end of the month, and will at least fix the fact that things break right now with a table that doesn't have a PK. It may not be ideal yet, but we will push that in later.
Sep 25, 2007 at 3:20 PM
I'm hitting the same thing with the templates where I have link tables.

Scenario :

I have a project table and a roles table. Roles are common throughout the database and not just related to projects. A project however can be made to be visible to only certain roles so I have a link table called ProjectRoles. The ProjectRoles table stores ProjectID and RoleID, linking the roles to the project. Both these fields are primary keys in their master tables, but can not be made primary keys in the link table.

I've worked on a lot of systems in the past where this structure is used to facilitate dynamic relationships between records. The insert update and delete SP's for these records also look a bit different in that they always take 2 parameters (both Id's). Also, when inserting the SP needs to make sure the combination is still unique so you don't get duplicates. I usualy add a constraint to the table to prevent misshaps.

Sep 25, 2007 at 3:33 PM
Thinking about this scenario again, I suspect that the best way of dealing with the Stored Procedure (SP) issue would probably be the following :

Since there is no way of letting the template know what the purpose of the table without the PK is, it needs to use a different set of rules to generate the related SPs. I suspect the best would be to generate insert, update and delete SPs that take every single field as a parameter. Since no PK is available to identify a record as unique, this would probably be the only way of finding the record to delete or update anyway. Of course every table will have it's own combination making a record unique, but this would be generic enough to place in a template.

This works for all the scenarios I've been able to come up with, including the one larsoncc1 is dealing with.

Just my 2 cents...
Sep 25, 2007 at 3:45 PM
K... found a way to make this work with the existing templates. This also inherently prevents duplicate records in my scenario (can't believe I didn't think of this before).

Defining a composite PK on the table solves my problem as now the templates have the PK they require. Also, this prevents duplicate records / combinations from being created in my tables.

Depending on the data you have larsoncc1, this might be a solution for you too... might be a bit of a stretch though.

Coordinator
Sep 26, 2007 at 3:01 PM
We're going to be creating an EntityBaseReadOnly class that tables without PKs will inherit from. This will hopefully be ready for the 2.0 release, but I'm not sure it will make it in at this point. The read-only entities will not support inserts, updates or deletes, mainly because we can't gaurantee that we have identified a particular record (and only one record) if no unique key is available. This will be ideal for lookup tables, and in the future, we may create a way to flag tables as lookup tables (even if they have PKs) and them be generated as read-only entities.

larsoncc1, would this solve your issues?