Hello all

I want to update the primary keys of an entity, and then update the entities that have Foreign key to those values, which means perform an update cascade in a certain entity.

Is there a clean way to do it through service studio?

Hello, Rita, 

Don't know if you can do this from within OutSystems, but you could try this approach using a SQL tool. 

https://stackoverflow.com/questions/18888973/changing-primary-key-value-for-a-database-row

You would need to copy the row, setting a different ID, and than update the FKs. Only after that you delete the original line. 

But I am not sure if this would work at all. Didn't try yet. 

Cheers 

Hello Eduardo


The problem is when you delete those rows you have the delete constraints (is set to Protect), so the problem remains

If you change the FK to the new ID before deleting the old row, this shouldn't happen. 

Hi Rita,

I prefer to follow the steps on this post to accomplish what you want.

Regards,

Marcelo

Hi, 

to update an entity attribute Identifier as a foreign key, you have to remove the referential integrity of the First table means which needs to update and also need to delete all the data of this Table. Then it can be done.

Marcelo Ferreira wrote:

Hi Rita,

I prefer to follow the steps on this post to accomplish what you want.

Regards,

Marcelo

I like that post :-) I also like the warning that in general, this doesn't make much sense for auto-numbered primary keys with no business meaning (which is what most OutSystems Ids should be).


I think the only problem using autonumber is when you need to migrate data, as the PK may change, while the FK will not, leading to an extra effort to keep things right. 

Other than that I like also the idea of binding PK and FK so that a change in one automatically changes the other. 

But... Isn't this (Alter Table) a DDL operation, and as such, not available from inside OutSystems? 

Eduardo,

You are correct, the default permissions for the runtime database user do not allow DDL operations... without access to the Configuration Tool this won't be a possible solution.

But then I don't think there is any simple solution, especially if the PK is an auto-number... as disabling this (even if temporarily) is also a DDL operation.

If the PK is not auto-number, you could try to perform all the changes on PK and all FKs within the same SQL tool, but I'm not sure that FK constraints aren't checked immediately...

Marcelo Ferreira wrote:

Hi Rita,

I prefer to follow the steps on this post to accomplish what you want.

Regards,

Marcelo

This was what we were trying to do, but I would like to know if there is a way to do it inside Outsystems. To have an update cascade for a key.


Hi Rita,

No method will work out of the box inside OutSystems because of the User used does not have permissions to do DDL, and all those operations to allow this is DDL.

Maybe you can create a special user in the database, with the right permissions, create a new connection to the OutSystems database using this user and use an extension to import the OutSystems entities from this connection (it is possible). Then you would be able to execute those methods from inside OutSystems (I think...).

But as you would need access to the database anyway, probably would be easier to do this directly in the database...