External table with composite keys

External table with composite keys

  

In my project i have to connect to external tables with more than one keys (composite keys). However, Outsystem only allows one column primary key. So, my team create a hand-made key by concatenate the composite key columns. So there is an Identifier type for the new table. 

I am not happy with this tricky. Imagine if the legacy system has so many tables with composite keys, factoring with the number of records of each table. It will be a nightmare just to create the new pk for outsystem.

And i believe that outsystem experts have anticipate this issue. So, my question is what is the best practise to integrate the legacy non-outsystem composite key tables into the outsystems? Thank you.

Regards,

A

You don't have to create concatenated composite primary key.

Just implement your own server-side logic for Create<Entity>, CreateOrUpdate<Entity>, Update<Entity>, Delete<Entity>, and optionally Get<Entity> and Get<Entity>ForUpdate.

Scaffolding might not create save button and logic flow, though...

Solution

Hi!

You can use unique indexes to implement a composite key.

Check out this topic.

Cheers,

Jaime

Solution

Hi, 

Picking up this thread (we also have external tables with compound keys):

@Jaime, do you mean you can use that too on external tables?

@Harlin, do you mean to create this in an action using sql or... ?

Regards, Harry



Hi Harry!

Can you be more specific when you refer to external tables?

I'm asking that because in OS there are no composite keys and we have to resort to indexes to do the job.

However, external tables may live in databases that allow composite keys. If so, use them. 

What happens is that the database engine will use the composite keys to validate records.

Regards,

Jaime

Jaime,

>>external tables may live in databases that allow composite keys

That's what I mean.Just wondering if the CRUD actions and scaffolding will work or if you have to do something yourself (and what)?

Regards, Harry 

Hi Harry,


You will have the validation of the composite key running at the database level. 

If the validation in the database fails, the action also fails, and a database error is raised in your application.

Does this answer your question?

Kind regards,

Jaime

Hi Jaime,

not quite. I wonder if the strong points of OS (speed in development) still are there. F. ex does Scaffolding screens, the automatic generation of CRUD actions, etc remain. If not, how do you 'workaround' this?  

Regards, Harry

Hi Harry,

Sorry if I was not clear.

When you scaffold screens for CRUDs they use actions (namely, Entity Actions) to store records in the database.

If a validation in the database fails, those actions also fail. The transaction is rolled back and you get a database error raised in your application.

So, you can keep using scaffolding to build your CRUDs and speed up your development. Database constraints you have are always validated (compound keys, unique indexes, foreign keys, delete rules, etc.).


Kind regards,

Jaime

Jaime,

thanks for clearing it out.

Regards, Harry