How do you handle a combined primary unique index with a sin

Posted by Ruben Dröge on 10-Dec-2018 08:41

I have a customer table with a combined primary unique index of two columns: custno, dossierno

The REST api has an URI /customers to retrieve all customers
The REST api has an URI /customers/{id} to retrieve a single customer

Question is, how would you solve this?

Solution 1:

- Use both values for custno and dossierno and a seperator in the uri like /customers/custno:dossierno

Solution 2:

- Create a primary unique index with one column to simplify things

Solution 3:

- Open to all other suggestions :)

All Replies

Posted by onnodehaan on 10-Dec-2018 09:24

Hi Ruben

This sounds a bit odd to me. This suggests that two customers can have the same customernumber for a different dossier?

If you can't further normalize the database, I would take the easiest approach; number 1. :-)

Posted by Ruben Dröge on 10-Dec-2018 09:28

Haha, I totally agree, but as you might have guessed it's not my database schema :)

Normalizing the database is definitely a good suggestion, but might be a stretch for the short term.

Posted by goo on 10-Dec-2018 11:56

You could maybe use ROWID
//Geir Otto

Posted by Ruben Dröge on 10-Dec-2018 12:11

True, but that has two disadvantages:

  • The ROWID does not mean anything to the user of the API
  • The ROWID changes when performing a dump & load
    (so this potentially breaks integrations with 3rd party systems using the REST api)

Posted by Rutger Olthuis on 10-Dec-2018 12:44

I've learned the hard way that it's always best to have a primary key be a single field. Preferable a (long)int.

Imho the custnum within the dossier is only a counter and should not have any meaning except for ordering. Use a unique cust_num (id..)

Posted by Akshay Guleria on 10-Dec-2018 12:48

I agree with [mention:ce4312d1e00542be918fe616c695a5de:e9ed411860ed4f2ba0265705b8793d05]. I would usually prefer to have a unique index on customer number if possible at all. In this case Solution 2 is simple to use.

But if normalisation in not possible at all then use Solution 1. The only thing I would like to mention is that do not use colon `:` as a delimiter. Colon `:` is a common character used in URLs. For clarity reasons I would use some other character like underscore `_` or hyphen `-`.

You can read more interesting stuff about URLs here, if you wish to.

Posted by sjellin.dovetail on 10-Dec-2018 13:05

In dynamics, we used a decimal identifier on all tables.

In smartcomponents, its a GUID if i recall correctly.

If you can't change the database (and depending on how you map out under the API) why can't you do /customers/{id}/{2ndid} ? You can then map each component of the URI to an index field.

Posted by Tim Hutchens on 10-Dec-2018 13:39


You may also consider modifying the REST annotations in your application so that both input parameters can be interpreted separately by the method being called. I think this is possible, but it might depend on how you are creating your REST service. Look for "@progress.service.resourceMapping" on this page: My examples are based on building a class that inherits from the Business Entity class. If you have full control of the REST API (both server and client), you have options like the one below to work with. You may also look up the WEB transport which gives you a lot more flexibility over how your API URI structure works. I can't guarantee the code below works, but am including it in case it points you in a direction that might help.

Essentially, the class file would have something like this at the top.

@progress.service.resource FILE(name="Customer", URI="/Customer", schemaName="ds_customer", schemaFile="Apps/AppServer/customer.i").

Then in the read method, you would have something like this:

@progress.service.resourceMapping(type="REST", operation="read", URI="/~{custno~}/~{dossierno~}", alias="", mediaType="application/json").



               INPUT dossierno AS CHARACTER,

    OUTPUT DATASET ds_customer):


       /* code to build filter based on custno and dossierno. */



Posted by goo on 10-Dec-2018 15:18

Yeps, that’s true :--

Posted by Peter Judge on 10-Dec-2018 16:30

OData uses one of 2 approaches - the one @hutct lays out (path segment) and one with a parenthesis-wrapping approach. See



The parens style would seem to me to be the better ("more correct") approach in the sense that path segments tend to be interpreted as parent-child although there's nothing in the spec that would indicate that they should be so <shrug>.  FWIW in PASOE webhandlers you _can_ define tokens to deal with the former (both actually) as below.

handler5=Example.API.OrderHandler: /OrderItems(OrderId={order-id},ItemNum={item-id})
handler6=Example.API.OrderHandler: /OrderItems/{order-id}/{item-id}

In your handler, you'd extract those values using the request

define variable orderId as integer no-undo.
define variable itemId as integer no-undo.
orderId = integer(poRequest:GetPathParameter('order-id')).
itemId  = integer(poRequest:GetPathParameter('item-id')).

I think I agree with the comments that an opaque id (like a ROWID or GUID or similar) isn't great , although that does veer into religious warfare so I'd just suggest not using a ROWID value as a ROWID.

Posted by Ruben Dröge on 11-Dec-2018 08:07

[mention:bfaee6d85eff467b8f2498e8c27f30ae:e9ed411860ed4f2ba0265705b8793d05] : I am not even considering using REST annotations :).

Using the new WebHandlers like @Peter Judge (pjudge) mentioned, which offers me a lot more flexibility.

However, as it turns out, this customer told me that dossierno is apparently there 'company number' (their multi-tenant field in all their tables).

So with that information I turned everything around and start every REST URI as follows:


Thanks all for the feedback!

This thread is closed