VALUES: A Truly Useless DDS Keyword
October 26, 2011 Hey, Ted
|
We had a recent crisis in our shop. When we dug to the root of the problem, we found that one of our files had an invalid value in one of the fields. I don’t understand why this was the case, because the programmer who wrote the DDS used the VALUES keyword on the field in question. Does the VALUES keyword not prevent invalid data from entering the database? –Paul No, Paul. One would think it does, but use of the VALUES keyword only applies to the workstation controller. That is, only the green-screen interface is restricted by the VALUES keyword. There’s nothing to stop an SQL interface or a high-level language program from writing another value to the database. Here’s an example of a physical file that uses the VALUES keyword. A R MYREC A ONEFIELD 5A A TWOFIELD 2A A REDFIELD 1P 0 VALUES(1 2 3) A BLUEFIELD 5P 2 A K ONEFIELD Obviously the author’s intention is that REDFIELD must have a whole-number value from 1 to 3. However, there is another way. Use a check constraint in SQL, as the following CREATE TABLE command illustrates. create table tholtp/qad3020pf
(onefield char(5),
twofield char(2),
redfield dec(1)
check (redfield between 1 and 3),
bluefield dec(5,2),
primary key (onefield))
Now the rule is defined in the database, and nothing can write a value other than 1, 2, or 3 into REDFIELD. Any attempt to write another value will raise error CPF502F (Check constraint violation on member MYFILE). Here’s yet one more reason to quit using DDS and start using SQL to define database tables. –Ted RELATED STORIES Handling Constraint Violations in RPG Simulate a Boolean Data Type in a Database Table Implementing Check Constraints in Referential Integrity
|
