Check constraint with wildcard

[Rickard Axne]

ORM check constraints in visio:

I want to add constraints with wildcards (eg [a-z][a-z][0-9]) in my ORM
source model. It seems to be logical to add this in the Value section of
Database properties for an object or value.

The problem is this. Visio isn't smart enough to realize the resulting
length of my constraint, in the example above char(3). Error Check
reports the length of my constraint to be too long for my data type.

So in order to find out what I'm doing wrong, I tried creating a
physical table with the check constraint and then reverse engineer it.
Well, in the ORM source model I can't find the constraint at all, and
it's not in the constraint report. However, When generating the logical
schema based on my reverse engineered ORM source, the constraint is
there after all?!

What am I missing?

Rickard

 

[Scot Becker]

Hi Rickard,

I want to add constraints with wildcards (eg [a-z][a-z][0-9]) in my ORM
source model. It seems to be logical to add this in the Value section of
Database properties for an object or value.

The problem is this. Visio isn't smart enough to realize the resulting
length of my constraint, in the example above char(3). Error Check
reports the length of my constraint to be too long for my data type.

I just tried this and it worked.

On the Value tab for the object, you would add "a" in the from box and "z"
in the to box (under the range section). You would similarly add ranges for
A-Z and 0-9.

I suspect that you added "a-z" in the value box, which Visio will interpret
as a 3 character string and not a range.

So in order to find out what I'm doing wrong, I tried creating a
physical table with the check constraint and then reverse engineer it.
Well, in the ORM source model I can't find the constraint at all, and
it's not in the constraint report. However, When generating the logical
schema based on my reverse engineered ORM source, the constraint is
there after all?!

I don't think that check clauses like this will get reverse engineered all
the way to an ORM value constraint; the parser is not that smart.

Hope that helps,
Scot.
................................................
Scot Becker

Orthogonal Software
www.orthogonalsoftware.com

ORM Blog: www.objectrolemodeling.com

To e-mail me, please use my first name at the domain listed above. All mail
sent to the reply-to address gets routed to a junk e-mail folder where it
will likely be deleted without being read.

 

[Rickard Axne]

Hi Scot,

I want to add constraints with wildcards (eg [a-z][a-z][0-9]) in my ORM
source model. It seems to be logical to add this in the Value section of
Database properties for an object or value.

The problem is this. Visio isn't smart enough to realize the resulting
length of my constraint, in the example above char(3). Error Check
reports the length of my constraint to be too long for my data type.

I just tried this and it worked.

On the Value tab for the object, you would add "a" in the from box and "z"
in the to box (under the range section). You would similarly add ranges for
A-Z and 0-9.

I suspect that you added "a-z" in the value box, which Visio will interpret
as a 3 character string and not a range.

Thanks, but i still can't accomplish this. What I want is the format
[A-Z][A-Z][0-9] in this case (eg 'SE3', 'US4', ..)

Thanks

 

[Rickard Axne]

Cheers Scot

Another question on the same subject, can I also add a simple comparison
check constraint in my orm model? I need this for ensuring max value is
greater than min value for some range.

Oh, I see what you are trying to do now....

ORM value constraints will constrain the entire value, they don't constrain
individual characters in the value. Thus, you could add a range constraint
for aaa to zzz, AAA to ZZZ, and 000 to 999, but that won't allow mixed case
(e.g. AbA) or mixed alpha and numeric (e.g. A12 or A1b).

Thus, you would have to implement this manually via a check clause and/or
stored procedure or via other programmatic means.

You can do this if it were a single character field, as in the attached.

Thanks, but i still can't accomplish this. What I want is the format
[A-Z][A-Z][0-9] in this case (eg 'SE3', 'US4', ..)

 

[Scot Becker]

Hi Rickard,

Another question on the same subject, can I also add a simple comparison
check constraint in my orm model? I need this for ensuring max value is
greater than min value for some range.

Unfortunately, not unless the max and min values are constant (e.g. between
1 and 5). If the Max and Min values are object types, you have to capture
this as a textual rule.

This used to exist for ORM in when the NIAM dialect was the defacto standard
and many people have requested that this be introduced into FORML (the ORM
dialect supported by VEA) for nearly a decade now.

The classic example is when a thing has start and end date facts and you
want to ensure that the end date is on or after the start date....

Kind Regards,
Scot.
................................................
Scot Becker

Orthogonal Software
www.orthogonalsoftware.com

ORM Blog: www.objectrolemodeling.com

To e-mail me, please use my first name at the domain listed above. All mail
sent to the reply-to address gets routed to a junk e-mail folder where it
will likely be deleted without being read.

 

[Rickard Axne]

Unfortunately, not unless the max and min values are constant (e.g. between
1 and 5). If the Max and Min values are object types, you have to capture
this as a textual rule.

This used to exist for ORM in when the NIAM dialect was the defacto standard
and many people have requested that this be introduced into FORML (the ORM
dialect supported by VEA) for nearly a decade now.

The classic example is when a thing has start and end date facts and you
want to ensure that the end date is on or after the start date....

Thanks for the reply, helpfull as ever. Strange for such a basic concept
to be lacking still!

Rickard