Adding check constraints to your database

I have previously written about other types of constraints: unique, primary key, and referential. In this post I am going to describe the check constraint, which is a way I can "push" validation of data into the database, rather than have the logic in all the programs that insert, update, or delete the data from the file or table.

I am going to use a table called PARENT again. I have added a couple of additional columns to it:

01 CREATE TABLE MYLIB.PARENT ( 02 PARENT_ID INTEGER NOT NULL, 03 LAST_NAME VARCHAR(30) NOT NULL, 04 FIRST_NAME VARCHAR(20) NOT NULL, 05 DATE_OF_BIRTH DATE NOT NULL, 06 START_DATE DATE NOT NULL, 07 STATUS CHAR(1) NOT NULL, 08 PRIMARY KEY (PARENT_ID), 09 CONSTRAINT PARENT_ID_CHECK CHECK(PARENT_ID > 0), 10 CONSTRAINT START_DATE_CHECK CHECK(DATE_OF_BIRTH < START_DATE) 11 ) ;

Most of the table is the same as before, except...

Line 6: START_DATE is the date the person started, what is not relevant to these examples.

Line 7: Status code to indicate the status of the row.

Line 8: I want a primary key constraint to prevent duplicate keyed rows.

Line 9: I added a check constraint to ensure that the parent id is greater than zero.

Line 10: And another check constraint to ensure that the start date must be greater than the date of birth.

After executing this statement. I can check the constraints using the TABLE_CONSTRAINTS view:

01 SELECT TABLE_NAME, 02 CONSTRAINT_NAME, 03 CONSTRAINT_TYPE 04 FROM SYSIBM.TABLE_CONSTRAINTS 05 WHERE TABLE_SCHEMA = 'MYLIB' 06 AND TABLE_NAME = 'PARENT'

The results show the three constraints:

TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE ---------- -------------------------------- --------------- PARENT Q_MYLIB_PARENT_PARENT_ID_00001 PRIMARY KEY PARENT PARENT_ID_CHECK CHECK PARENT START_DATE_CHECK CHECK

I have my primary and my two check constraints.

I am using ACS's "Run SQL Scripts" to evaluate these constraints. My first test is to attempt to insert a row into the table with a person id that is less than zero:

01 INSERT INTO PARENT VALUES(-1,'SMITH','JOHN','1960-01-01',CURRENT_DATE,' ')

The insert errors with the following:

SQL State: 23513 Vendor Code: -545 Message: [SQL0545] INSERT, UPDATE, or MERGE not allowed by CHECK constraint. Cause . . . . . : The value being inserted or updated does not meet the criteria of CHECK constraint PARENT_ID_CHECK. The operation is not allowed. Recovery . . . : Change the values being inserted or updated so that the CHECK constraint is met. Otherwise, drop the CHECK constraint PARENT_ID_CHECK.

The message clearly explains that the PARENT_ID_CHECK is what failed.

And for the start date check constraint:

01 INSERT INTO PARENT VALUES(1,'SMITH','JOHN',CURRENT_DATE,'2025-01-01',' ')

The date of birth is the fourth column, and the start date is the fifth. As the start date is less than the date of birth the following error happens:

SQL State: 23513 Vendor Code: -545 Message: [SQL0545] INSERT, UPDATE, or MERGE not allowed by CHECK constraint. Cause . . . . . : The value being inserted or updated does not meet the criteria of CHECK constraint START_DATE_CHECK. The operation is not allowed. Recovery . . . : Change the values being inserted or updated so that the CHECK constraint is met. Otherwise, drop the CHECK constraint START_DATE_CHECK.

The message shows which check constraint failed.

I can add constraints to an existing table using the ALTER TABLE SQL statement. For example, I can add a check constraint for the status column:

01 ALTER TABLE PARENT 02 ADD CONSTRAINT STATUS_VALUES CHECK (STATUS IN ('A','I','D'))

Line 2: The status can only contain these three characters, even blank is invalid.

I can show that this constraint was added to the table with the TABLE_CONSTRAINTS view:

01 SELECT TABLE_NAME, 02 CONSTRAINT_NAME, 03 CONSTRAINT_TYPE 04 FROM SYSIBM.TABLE_CONSTRAINTS 05 WHERE TABLE_SCHEMA = 'MYLIB' 06 AND TABLE_NAME = 'PARENT'

I can see that I now have another constraint, STATUS_VALUES.

TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE ---------- -------------------------------- --------------- PARENT Q_MYLIB_PARENT_PARENT_ID_00001 PRIMARY KEY PARENT PARENT_ID_CHECK CHECK PARENT START_DATE_CHECK CHECK PARENT STATUS_VALUES CHECK

I can evaluate this constraint in "Run SQL Scripts" with the following statement:

01 INSERT INTO PARENT VALUES(1,'SMITH','JOHN','1960-01-01',CURRENT_DATE,'X')

The last column I am trying to insert is the status. And I know that 'X' is not valid. Which means I get a message:

SQL State: 23513 Vendor Code: -545 Message: [SQL0545] INSERT, UPDATE, or MERGE not allowed by CHECK constraint. Cause . . . . . : The value being inserted or updated does not meet the criteria of CHECK constraint STATUS_VALUES. The operation is not allowed. ecovery . . . : Change the values being inserted or updated so that the CHECK constraint is met. Otherwise, drop the CHECK constraint STATUS_VALUES.

I want to change the check for STATUS. What I need to do first is to drop the existing check constraint for it:

01 ALTER TABLE PARENT 02 DROP CONSTRAINT STATUS_VALUES

Line 2: I drop the constraint STATUS_VALUES.

Add add another:

01 ALTER TABLE PARENT 02 ADD CONSTRAINT STATUS_RANGE CHECK (STATUS BETWEEN 'A' AND 'H')

I have created a new constraint, STATUS_RANGE to check that the value in the status column is in the range 'A' to 'H'.

I try to make the same SQL statement as before:

01 INSERT INTO PARENT VALUES(1,'SMITH','JOHN','1960-01-01',CURRENT_DATE,'X')

This fails as 'X' is not in the permitted range of values, and an error is returned:

SQL State: 23513 Vendor Code: -545 Message: [SQL0545] INSERT, UPDATE, or MERGE not allowed by CHECK constraint. Cause . . . . . : The value being inserted or updated does not meet the criteria of CHECK constraint STATUS_RANGE. The operation is not allowed. Recovery . . . : Change the values being inserted or updated so that the CHECK constraint is met. Otherwise, drop the CHECK constraint STATUS_RANGE.

How can I capture these check constraint errors in a RPG program? I have a program to demonstrate. Let me start with the first 12 lines of it:

01 **free 02 ctl-opt option(*srcstmt) dftactgrp(*no) ; 03 dcl-pr GetDiagnostics char(32900) ; 04 end-pr ; 05 dcl-ds GetDiag qualified ; 06 MessageId char(10) ; 07 MessageLength int(5) ; 08 MessageText varchar(32740) ; 09 ReturnedSQLCode char(5) ; 10 ReturnedSQLState char(5) ; 11 ConstraintName varchar(128) ; 12 end-ds ;

Line 2: My favorite control options.

Lines 3 and 4: I am going to use SQL's GET DIAGNOSTICS to give me happened in a SQL statement. I am placing it in its own procedure, therefore, I need a procedure prototype for it. I am not passing any parameters to it, but I am receiving a character variable of 32,900 bytes from it.

Line 5 - 12: That 32,900 characters happens to be this data structure. This contains the data elements I want returned from the procedure to the calling program.

The first attempted insert is the one that will fail due to the person id being less than one:

13 exec sql INSERT INTO PARENT VALUES(-1,'SMITH','JOHN','1960-01-01',CURRENT_DATE,'A') ; 14 GetDiag = GetDiagnostics() ; 15 if (GetDiag.ReturnedSqlCode <> ' ') ; 16 dsply '1. There was an error' ; 17 endif ;

Line 13: The SQL insert statement, that we all know will fail.

Line 14: I call the GetDiagnostics procedure, and I place what it returns into the GetDiag data structure.

Line 15: If the returned SQL code is not blank, then I display that there is an error.

I started debug and placed a breakpoint at line 17. When the breakpoint is reached I looked at what values GetDiag contained:

DSPLY 1. There was an error GETDIAG.MESSAGEID = 'SQL0545 ' GETDIAG.MESSAGELENGTH = 57 GETDIAG.MESSAGETEXT = 'INSERT, UPDATE, or MERGE not allowed by CHECK constraint.' GETDIAG.RETURNEDSQLCODE = '-545 ' GETDIAG.RETURNEDSQLSTATE = '23513' GETDIAG.ROWSCOUNT = 0 GETDIAG.CONSTRAINTNAME = 'PARENT_ID_CHECK'

All of the subfields tell me that the SQL statement errored. The last subfield in the data structure gives me the name of the check constraint that failed. Which I could use to analyze why this statement failed.

The next SQL statement will fail the date check constraint.

18 exec sql INSERT INTO PARENT VALUES(1,'SMITH','JOHN',CURRENT_DATE,'2025-01-01','A') ; 19 GetDiag = GetDiagnostics() ; 20 if (GetDiag.ReturnedSqlCode <> ' ') ; 21 dsply '2. There was an error' ; 22 endif ;

Line 18: This is the same as I evaluated in the "Run SQL Scripts".

Lines 19 – 22: Are the same as lines 14 – 17.

I had placed a debug breakpoint on line 22. When the breakpoint is reached I checked the contents of GetDiag:

DSPLY 2. There was an error GETDIAG.CONSTRAINTNAME = 'START_DATE_CHECK'

The only subfield that is different from what I showed before is the constraint name, which shows that the START_DATE_CHECK failed.

When I performed the next test the STATUS_VALUES constraint was active for the status column.

23 exec sql INSERT INTO PARENT VALUES(1,'SMITH','JOHN','1960-01-01',CURRENT_DATE,'X') ; 24 GetDiag = GetDiagnostics() ; 25 if (GetDiag.ReturnedSqlCode <> ' ') ; 26 dsply '3. There was an error' ; 27 endif ;

The last column is the status, and I know 'X' is not valid. Therefore, when I reach the next debug breakpoint, at line 27, and I look at GetDiag I see:

DSPLY 3. There was an error GETDIAG.CONSTRAINTNAME = 'STATUS_VALUES'

Which is what I expected.

In the next rest my SQL statement had all three errors: parent id less than 1, date of birth greater than start date, and the status of 'X'.

28 exec sql INSERT INTO PARENT VALUES(-1,'SMITH','JOHN',CURRENT_DATE,'2025-01-01','X') ; 29 GetDiag = GetDiagnostics() ; 30 if (GetDiag.ReturnedSqlCode <> ' ') ; 31 dsply '4. There was an error' ; 32 endif ;

How would get diagnostics show this?

DSPLY 4. There was an error GETDIAG.CONSTRAINTNAME = 'STATUS_VALUES'

It shows the error from last added check constraint.

Below is my code for the GetDiagnotics procedure:

34 dcl-proc GetDiagnostics ; 35 dcl-pi *n char(32900) ; 36 end-pi ; 37 exec sql GET DIAGNOSTICS CONDITION 1 38 :GetDiag.ReturnedSqlCode = DB2_RETURNED_SQLCODE, 39 :GetDiag.ReturnedSQLState = RETURNED_SQLSTATE, 40 :GetDiag.MessageLength = MESSAGE_LENGTH, 41 :GetDiag.MessageText = MESSAGE_TEXT, 42 :GetDiag.MessageId = DB2_MESSAGE_ID, 43 :GetDiag.ConstraintName = CONSTRAINT_NAME ; 44 return GetDiag ; 45 end-proc ;

Lines 35 and 36: The procedure interface, with no incoming parameter and a returning parameter.

Lines 37 – 43: I have shown examples of the GET DIAGNOSTICS in previous posts. I have removed a couple of subfields from what I have shown before for clarity. And I have added the CONTRAINT_NAME, line 43, to allow me to return the name of the check constraint that failed.

This has been simple examples of using check constraints. Now you can understand that by using check constraints the validation of the data can be independent of programs, and this will prevent someone using DFU or ODBC to update the data in a way that is counter to the databases rules.

 

This article was written for IBM i 7.6, and should work for some earlier releases too.