Solved

Why is Check constraints only on tables and not on Columns or even Domains?

  • 6 July 2020
  • 4 replies
  • 102 views
R
Rank
Userlevel 3
Badge +5

Check constraints is only defined on tables. However it could be helpful to define constraints on columns or even domains. Searching on internet I find references that constraints on domains have the advantage of defining them just once instead of defining them for every use. I have not found any information in the community, so I assume there is a reason why constraints are only defined on a table.

So what reasons are there for only defining a constraint on a table instead of also defining them on columns and domains?

icon

Best answer by Anne Buit 8 July 2020, 09:27

View original

4 replies

Anne Buit
Rank
Userlevel 7
Badge +5

Hi Roy,

Not all database platforms that we support allow for the type of constraint creation that you describe. SQL Server supports the binding of rules on columns and user defined datatypes but the feature is in maintenance mode. The usage of check constraints is endorsed.

To keep the transformation from the platform-independent model to the the rdbms-specific scripts consistent we've chosen the most broadly supported feature for these kind of data integrity checks.

Table check constraints should be automatically created based on settings defined on the domain level in the Software Factory. One setting may affect many tables this way. It should not limit developers in any way that this feature is implemented using table check constraints.

However, if there are functional aspects that you'd like to see improved when it comes to data integrity checks, we'd love to hear this.

R
Rank
Userlevel 3
Badge +5

Thanks Anne,

Seems logical.

I am wondering if it could be possible to define a constraint on domain in TSF and on deployment transfer them to the table (for those databases that don't support them at domain level)? In this way it is possible to define the constraint once and use them for all columns that make use of that domain.

This reduces the chance of human-error (which I'm bound to make ;-) ) when changing one and missing another.

Anne Buit
Rank
Userlevel 7
Badge +5

Hi Roy,

I assume you are talking about an SQL-expression based constraint? Because min/max and element-based constraints are based on the domains in the TSF.

Anne Buit
Rank
Userlevel 7
Badge +5

The SQL-expression based constraints could be added at domain-level, feel free to add an idea in the ideation section.

Do keep in mind that the expressions are allowed to use other columns, potentially limiting the useage of a domain to tables with certain columns present.

In the meanwhile, it's possible to use the dynamic model to automatically add constraints to all columns using a certain domain.

Reply


Terms & ConditionsCookie settings