foreign key constraints with DB catalogs

foreign key constraints with DB catalogs

  

Hi,

I would like to know if there is any way with which foreign key constraints can be managed for tables in other catalogs ?

i was having a cleanup activity at Main catalog where some records from the users tables were cleaned up. Before cleaning, the constraints gave all the referred table and columns in the main catalog only and they were handled accordingly. But later on i came to know that those cleaned/deleted records (of users table) were in use in tables of other catalogs. Here, in spite of reflecting the foreign keys (to tables in other catalogs) in service studio, it did not prompted before deleting the records from users table. Later i checked that even if we are declaring foreign keys for users table in different catalog, any number can be passed on to be saved on those attributes. (It does not prompt for foreign key constraint error).

For the time being, the only solution i see is to write a script having join between system entities like ossys_entity, ossys_entity_attr,  ossys_espace, OSSYS_DBCATALOG to find out the references of User table and check one by one for the id's being used. But this is a long process and is not ideal if we really don't know for any other tables are having references or not.

Any suggestions to control this can be helpful.



Hi Debasis,

Unfortunately you cannot enforce foreign key constrain between entities in two different catelogs. The foreign key constrain only work for the entities in the same catelog.

The work around for this is to you can have a trigger (before delete) and check there for child records against the entity id.


Regards

-PJ-


PRAMOD JAIN wrote:

Hi Debasis,

Unfortunately you cannot enforce foreign key constrain between entities in two different catelogs. The foreign key constrain only work for the entities in the same catelog.

The work around for this is to you can have a trigger (before delete) and check there for child records against the entity id.


Regards

-PJ-


Thanks for the reply Pramod,

but here again the problem statement will remain the same as we cannot identify from the trigger about the child records at other catalog. And this is not just about the users table, there may be several system entities with widespread references in other catalogs. I am more looking towards a generic solution.

Moreover, in service studio it is showing that it has got a reference to user table for instance. However, in the back end it doesn't. I hope Outsystems will make some sort of ease in the future releases.


This is a limitation of SQL Server, not the platform.

https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-foreign-key-relationships?view=sql-server-2017

"FOREIGN KEY constraints can reference only tables within the same database on the same server. Cross-database referential integrity must be implemented through triggers."

The suggestion in that article is to use a trigger, but, again, that is something you'll have to do at the database level.

Carlos Ribeiro da Fonseca wrote:

This is a limitation of SQL Server, not the platform.

https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-foreign-key-relationships?view=sql-server-2017

"FOREIGN KEY constraints can reference only tables within the same database on the same server. Cross-database referential integrity must be implemented through triggers."

The suggestion in that article is to use a trigger, but, again, that is something you'll have to do at the database level.


correct, that's what next release of outsystems should take care of automatically. I posted this in Idea section as well.