UNIQUE PRIMARY INDEX Cancel

1. Roger Blanchard

   

   381 posts
   Registered:
   29 Jun 2018
   30 Jan 2019

   Link to this post

   For most of our "totals" tables in our DB the PRIMARY index is not unique. I just want to make sure I am not missing anything. If we create a BE for these tables we will not be able to fetch data using the SCL. I understand CRUD operations would be an issue but wanted to confirm fetching is not allowed either.
2. Mike Fechner

   

   319 posts
   Registered:
   14 Sep 2016
   Answered

   30 Jan 2019 in reply to Roger Blanchard

   Link to this post

   There are a few options - and two situations.
   
   For reading data into the ProDataset, the temp-table needs to have a primary unique index. That index does not need to be in the database.
   
   For updating, the Data-Source objects need to be able to find the right DB record based on the temp-table before record. That's what the DB needs to have something unique for.
   
   Easiest is a unique index.
   
   If there's a field combination that's de facto unique (enough for the records in question) you can use the KEYS option, e.g. KEYS (Customer.CustNum). You can enter the keys option in the temp-table properties of the Business Entity Designer.
   
   If there's no unique field combination (shame on you for violating basic relational principles), you can add a field of type ROWID in the temp-table, map that to ROWID(Customer) - you can enter it like that in the field source in the business entity designer. In the data-source keys phrase, use KEYS (ROWID).
   
   See the "DEFINE DATA-SOURCE Statement" online help for more details.
   
3. Roger Blanchard

   

   381 posts
   Registered:
   29 Jun 2018
   30 Jan 2019 in reply to Mike Fechner

   Link to this post

   Thanks Mike.
4. Roger Blanchard

   

   381 posts
   Registered:
   29 Jun 2018
   02 Feb 2019 in reply to Mike Fechner

   Link to this post

   For reading data into the ProDataset, the temp-table needs to have a primary unique index. That index does not need to be in the database.
   
   [RRB] We can add a unique index to the TT...that will work. Thanks.
   
   For updating, the Data-Source objects need to be able to find the right DB record based on the temp-table before record. That's what the DB needs to have something unique for.
   
   Easiest is a unique index.
   
   If there's a field combination that's de facto unique (enough for the records in question) you can use the KEYS option, e.g. KEYS (Customer.CustNum). You can enter the keys option in the temp-table properties of the Business Entity Designer.
   
   [RRB] We do have a unique combination so we can use the KEYS option for now. Thanks.
   
   If there's no unique field combination (shame on you for violating basic relational principles), you can add a field of type ROWID in the temp-table, map that to ROWID(Customer) - you can enter it like that in the field source in the business entity designer. In the data-source keys phrase, use KEYS (ROWID).
   
   [RRB] Many of these tables were added to the DB before I was at Osprey...had no control over it. I have been cleaning up the DB for years. We have it on our DB schema list to add a unique primary index on all these tables. Thanks.