Sql Trigger or Key Constraint or Check Constraint

[new]

Sorry to bug you with what is a simple question but if you could point me in
the right direction I can follow up.

I have a sql table field we can call XXX it is the least significant of a
four field primary key (aaa bbb ccc).

When XXX is of the form A99 (i.e. the letter A followed by two digits) I
want to ensure that it is the only aaa bbb ccc of that form.

If I had a function ISof FormA99(XXX) that returned either "T" when it was
or "F" || XXX my key constraint would be that aaa bbb ccc ISof FormA99(XXX)
is unique.

My question is: how do I do this? If there was such a thing (is there?) as
a derived field then I would create the function and use a unique
constraint. I am sure there are many ways to do this. This table will be
small (1000 rows at most) and change infrequently.

Thanks

[Knut Stolze]

new wrote:

Sorry to bug you with what is a simple question but if you could point me
in the right direction I can follow up.

I have a sql table field we can call XXX it is the least significant of a
four field primary key (aaa bbb ccc).

When XXX is of the form A99 (i.e. the letter A followed by two digits) I
want to ensure that it is the only aaa bbb ccc of that form.

Can XXX contain values that are _not_ in the form A\d\d (perl regexp
syntax)? If so, what's supposed to happen then? If not, then I believe
that XXX is your only primary/unique key. Otherwise, you will need a
trigger because check constraints operate on a single row only.

If I had a function ISof FormA99(XXX) that returned either "T" when it was
or "F" || XXX my key constraint would be that aaa bbb ccc ISof
FormA99(XXX) is unique.

You treat the values A12 and A78 as the same in that case? But 'A76'
and 'CDE' are different while 'ABC' and 'XYZ' are treated as equal again?
If so, you can simply create this function of yours (make it DETERMINISTIC
and NO EXTERNAL ACTION), add a GENERATED ALWAYS column (based on the
function) to your table, and use that column in your primary/unique key.

p.s: If possible, you should think about your database design. It seems to
be rather strange to have something in a primary/unique key that doesn't
really belong to the primary/unique key at all - but rather some derived
value.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[new]

Knut, thanks for your response!

Your last comment first: I entirely agree - now that I realize that one can
create a generated column with db2 I entirely agree that it is the generated
column that should be in the unique primary key.

I am having a problem with using "generated always" with a char column - am
I missing something? (V5r2)

I believe your second reading of my code was correct: A\d\d would mean that
A12 and A78 would be the same, 'A76'
and 'CDE' are different but (NOTE DIFFERENCE FROM YOUR RESPONSE) 'ABC' and
'XYZ' are NOT treated as equal

So can I use a generated function with a char column (in V5r2)?
"Knut Stolze" <st****@de.ibm.comwrote in message
news:eg**********@lc03.rz.uni-jena.de...

new wrote:

>Sorry to bug you with what is a simple question but if you could point me
in the right direction I can follow up.

I have a sql table field we can call XXX it is the least significant of
a
four field primary key (aaa bbb ccc).

When XXX is of the form A99 (i.e. the letter A followed by two digits) I
want to ensure that it is the only aaa bbb ccc of that form.

Can XXX contain values that are _not_ in the form A\d\d (perl regexp
syntax)? If so, what's supposed to happen then? If not, then I believe
that XXX is your only primary/unique key. Otherwise, you will need a
trigger because check constraints operate on a single row only.

>If I had a function ISof FormA99(XXX) that returned either "T" when it
was
or "F" || XXX my key constraint would be that aaa bbb ccc ISof
FormA99(XXX) is unique.

You treat the values A12 and A78 as the same in that case? But 'A76'
and 'CDE' are different while 'ABC' and 'XYZ' are treated as equal again?
If so, you can simply create this function of yours (make it DETERMINISTIC
and NO EXTERNAL ACTION), add a GENERATED ALWAYS column (based on the
function) to your table, and use that column in your primary/unique key.

p.s: If possible, you should think about your database design. It seems
to
be rather strange to have something in a primary/unique key that doesn't
really belong to the primary/unique key at all - but rather some derived
value.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Knut Stolze]

new wrote:

Knut, thanks for your response!

Your last comment first: I entirely agree - now that I realize that one
can create a generated column with db2 I entirely agree that it is the
generated column that should be in the unique primary key.

I am having a problem with using "generated always" with a char column -
am
I missing something? (V5r2)

I believe your second reading of my code was correct: A\d\d would mean
that A12 and A78 would be the same, 'A76'
and 'CDE' are different but (NOTE DIFFERENCE FROM YOUR RESPONSE) 'ABC' and
'XYZ' are NOT treated as equal

Then your description wasn't complete. You were only talking about A
followed by two digits. What's supposed to happen in all other cases?

So can I use a generated function with a char column (in V5r2)?

I don't know enough about DB2 for iSeries to comment. You didn't state your
platform, so I assumed DB2 for LUW.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[new]

ok the function in my case would transform:
if arg of form A\d\d then '---' else arg

"Knut Stolze" <st****@de.ibm.comwrote in message
news:eg**********@lc03.rz.uni-jena.de...

new wrote:

>Knut, thanks for your response!

Your last comment first: I entirely agree - now that I realize that one
can create a generated column with db2 I entirely agree that it is the
generated column that should be in the unique primary key.

I am having a problem with using "generated always" with a char column -
am
I missing something? (V5r2)

I believe your second reading of my code was correct: A\d\d would mean
that A12 and A78 would be the same, 'A76'
and 'CDE' are different but (NOTE DIFFERENCE FROM YOUR RESPONSE) 'ABC'
and
'XYZ' are NOT treated as equal

Then your description wasn't complete. You were only talking about A
followed by two digits. What's supposed to happen in all other cases?

>So can I use a generated function with a char column (in V5r2)?

I don't know enough about DB2 for iSeries to comment. You didn't state
your
platform, so I assumed DB2 for LUW.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Kent Milligan]

DB2 for i5/OS only supports the GENERATED ALWAYS AS IDENTITY clause for numeric
columns - that support was added initially in V5R2.

new wrote:

ok the function in my case would transform:
if arg of form A\d\d then '---' else arg

"Knut Stolze" <st****@de.ibm.comwrote in message
news:eg**********@lc03.rz.uni-jena.de...

>new wrote:

>>Knut, thanks for your response!

Your last comment first: I entirely agree - now that I realize that one
can create a generated column with db2 I entirely agree that it is the
generated column that should be in the unique primary key.

I am having a problem with using "generated always" with a char column -
am
I missing something? (V5r2)

I believe your second reading of my code was correct: A\d\d would mean
that A12 and A78 would be the same, 'A76'
and 'CDE' are different but (NOTE DIFFERENCE FROM YOUR RESPONSE) 'ABC'
and
'XYZ' are NOT treated as equal

Then your description wasn't complete. You were only talking about A
followed by two digits. What's supposed to happen in all other cases?

>>So can I use a generated function with a char column (in V5r2)?

I don't know enough about DB2 for iSeries to comment. You didn't state
your
platform, so I assumed DB2 for LUW.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

--
Kent Milligan
ISV Enablement - System i
km***@us.eye-bee-m.com (spam trick) GO HAWKEYES!!

>>ibm.com/iseries/db2

(opinions stated are not necessarily those of my employer)