Database constraints

Database constraints

  

On a recent project i was working on, we had a scenario where a broker was to be able to create records straight into the database instead of going through Outsystems platform.

To my surprise, most of the tables created by the Outsystems platform did not enforce the constraints as instructed when defining the Entity attributes.


Example: All the attributes in the Person Entity, Is Mandatory property was set to Yes. (See attached image)

But when inspecting the database tables, all the columns except the primary ID columns constraints where set to Allow Nulls.



After talking to some other community members, this behavior seems to be by design.

My question, how can one maintain the integrity of the data if not using the Outsystems platform?

Hi,

I see two possible approaches:

1. Will the broker use some other application to introduce the data into the database? The validation could be performed in this other application.

2. You could use database triggers to validate the data being inserted.

Hi Aurelio,

1: In my opinion this approach requires proper documentation and discipline, something i don't see many companies/people have and its even harder when dealing with DBAs.

 

2: Probably using triggers, bug again this means circumventing the OutSystems platform? since you can not manager triggers with the platform? In my opinion this also introduces another complexity when it comes to deployment and version control, Meaning you have to create SQL Scripts and deploy them manually on all the SQL Server and every time you modify your data model you have to repeat this process again.

Hi capsuline,

I think the second approach is the best, since it will give you, the developer, better control over the consistence of your application's database. Yes, it will circumvent Outsystems, but it sounds like you don't really have much of a choice, since your requirement is to insert data into the DB without using the platform anyway.

You're also right when you say that this approach would require extra work when deploying new versions of your app. But again, since the requirement is to insert data without using Outsystems, I don't think you have much of a choice.

Couldn't the broker at least use webservices developed by you in Outsystems to insert the data?

Hello Capsuline,

I would really question how this data can be created in OS DB tables. Apparently someone has acces to do something he/she shouldn't do. So I would find out who could do this and ristrict the rights on the tables.

Or it's by design but then the answer of Aurelio is applied.

Kind regards,
Evert

Hi Evert,

Its by design, the enterprise service bus(broker) has access to all databases including the ones created by the Outsystems platform.
But i think your right, maybe we need to suggest to the organisation to restrict access to these databases and create services for each as @Aurelio Santos suggested. Probably this is the best approach to maintain data integrity.

Solution


Hi Capsuline,

I would really suggest that yes. When the enterprise service bus need to insert data, build a service or API in OS. Then you always can validate the inputted data.

Kind regards,
Evert

Solution

Another way to control this is to create your database tables outside of OutSystems and use the integration studio to enable OutSystems access to them. Then you can create your tables exactly how you want them to be, allowing the DBMS to enforce the rules you want, while still being able to use OutSystems for development. You will lose some of the power of OutSystems to easily change you database but it may be worth it to enable the database you really want.

Then still I would suggest to keep OS datamodel in OS and if you need to insert data into it, do it trough services.