Distinctly weak type rules with a constrained twist
Back in the early 1990s when I first started working on database language architecture, DB2 for Linux, Unix, and Windows was a new DB2 database manager (I believe we called it DB2 for common servers back then) and making a big jump in the SQL that it supported along with implementing the Starburst compiler with the research team from the Almaden Research Center. During that time, we started to make SQL more user-extensible with user-defined objects for procedures, functions and data types. The first kind of user-defined data type was the distinct type. User-defined distinct types provided a way for the SQL user to give names to data types which provides a self-documenting way of of defining relationships between columns, parameters, and variables. The key feature of the distinct type was strong typing - that is, it is distinct from all the other data types except for assignment to built-in SQL data type on which it was based. Even if 2 distinct types like HATSIZE and SHOESIZE that have the same base SQL type like SMALLINT, they can not be directly assigned to each other or compared to each other within SQL operations. When you defined a distinct type, a set of comparison functions was implicitly generated to allow comparison of operands with the same distinct type and a set of functions to cast between the distinct type and it's base type (in some cases like distinct types based on a fixed char type it even generated a function to cast from the data type of the constant associated with the data type family of the base data type, in this case a varchar). Overall, a nice feature.
Weakly-typed distinct types
A feature added in DB2 10.1 Fix Pack 2 should provide those users what they would prefer. There is a now a way to create a distinct type that has weak typing rules instead of strong typing rules. When a distinct type is defined with weak type rules, it effectively acts as an alias for the SQL data type specified as the base data type. Just like a strongly-typed distinct type, this new form of distinct type can be used for columns, routine parameters, local, or global SQL variables. For example, if my application was dealing with colour codes, I could define a distinct type like this:
CREATE TYPE COLOURCODE AS CHAR(3) WITH WEAK TYPE RULES
The distinctly new clause here is "WITH WEAK TYPE RULES" which pretty clearly spells out how this is different from the strong type rules that had been the only form of distinct type since Version 2. By the way, you can now explicitly specify in the syntax that you want WITH STRONG TYPE RULES which will continue as the default when defining a distinct type.
Constraining the distinct type values
By now you may be wondering about the "constrained twist" I referred to in the title.
CREATE TYPE COLOURCODE AS CHAR(3) WITH WEAK TYPE RULES NOT NULL
The real constraint flexibility comes with the capability to specify a data type check constraint with your weak type definition that looks very much like a table check constraint. The thing you need to know is how to refer to the value that you want to check within the check condition you specify. It is simply called VALUE. Easy enough to remember, right?
CREATE TYPE COLOURCODE AS CHAR(3) WITH WEAK TYPE RULES NOT NULL CHECK (VALUE IN ('RED', 'GRN', 'BLU'))
Now we can try out the new data type using a global variable.
create variable colour colorcode DB20000I The SQL command completed successfully. set colour='GRN' DB20000I The SQL command completed successfully. values colour 1 --- GRN 1 record(s) selected.
So far, so good. We have a variable defined and it allowed a valid value. What happens with an invalid value?
set colour='YEL' SQL20552N The cast or assignment failed because the value does not conform to the data type constraint of the user-defined type. User-defined type: "SWA
This simple example shows that an assignment to variable of type COLOURCODE using a value that is excluded by the data type constraint causes an error to be returned. I like being able to add a data rule for the data type once and then let the system manage the checking for you when ever there is an assignment or a cast to that data type. Same thing happens using a cast specification.
values cast('PNK' as colourcode) 1 --- SQL20552N The cast or assignment failed because the value does not conform to the data type constraint of the user-defined type. User-defined type: "SWA
CREATE TYPE POSITIVEN AS INTEGER WITH WEAK TYPE RULES NOT NULL CHECK (VALUE BETWEEN 1 AND 2147483647)
You can also do similar statements for POSITIVE, NATURAL and NATURALN.
There is a catch though when a data type constraint is specified. These data type constraints can only be used at the programming levels for variables and parameters. A weakly-typed distinct type with data type constraints can not be used as a column data type. Determining the appropriate behaviours for various actions involving both table check constraints and data type check constraints posed some interesting semantic and implementation challenges. Instead of holding back the capability from the use in the world of SQL PL, we proceeded with the restriction. The work around is to define the column with the base type, matching nullability constraint, and a table check constraint that matches the data type check constraint. This work around does not help with a scenario where you want to change something about the weakly-typed distinct type. Here's an example with the work around.
CREATE TABLE COLOURS (CODE CHAR(3) NOT NULL CHECK (CODE IN ('RED', 'GRN', 'BLU')), DESCRIPTION VARCHAR(30))
Now I'll write a little procedure to insert a row in this table, but I'm going to intentionally make a sloppy coding error.
CREATE PROCEDURE ADDCOLOUR (IN CODE COLOURCODE, DESC VARCHAR(30)) BEGIN DECLARE C1 COLOURCODE; INSERT INTO COLOURS VALUES (C1, DESC); END CALL ADDCOLOUR ('GRN', 'GREEN') SQL20552N The cast or assignment failed because the value does not conform to the data type constraint of the user-defined type. User-defined type: "SWA
What happened here? Well, I purposely neglected to do the assignment from the parameter called CODE to the local variable C1 so this would return the error. My first use of the local variable C1 occurs in the INSERT statement. This first reference checks that the value is a valid COLOURCODE. Except, as the error message indicates, C1 defaults to the value NULL, which violates the NOT NULL constraint. While I clearly meant to assign value to C1, the important message here is that when a weakly-typed distinct type is defined to be not null, any variables defined using the type should either have a non-null default specified or you need to ensure that the first reference is an assignment.