Basic constraints with SQL tables

This is another one of those posts I did not realize I had not written about, database constraints.

Constraints allow me to move the control the integrity of my data into the database itself. I am going to give some simple examples of what can be achieved with constraints with DDL Tables. Here I am going to cover the following:

1. Unique constraint
2. Primary key constraint
3. Foreign key constraint

There are other types of constraints, but for this post I am only going to write about these.

 

Unique constraint

A unique constraint ensures that the rows in a Table are unique. Which is what we want, as we do not want to have Tables containing duplicate Employee ids, or Customer numbers.

This is my example of a Table created with a unique constraint:

01 CREATE TABLE MYLIB.PARENT1 ( 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 UNIQUE (PARENT_ID))

Line 6: The UNIQUE keyword ensures that my Table will not allow duplicate PARENT_ID values.

I can use the TABLE_CONSTRAINTS View to check the type of constraint I have for this Table:

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

Lines 1 – 3: I am only interested in three columns:

1. TABLE_NAME:  Name of the Table
2. CONSTRAINT_NAME:  Name of the constraint
3. CONSTRAINT_TYPE: Type of constraint

The result for this statement is:

TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE ---------- --------------------------------- --------------- PARENT1 Q_MYLIB_PARENT1_PARENT_ID_00001 UNIQUE

The name of the constraint is automatically, and the constraint type columns informs us that it is unique.

I can get more information from the SYSFILES View:

01 SELECT SYSTEM_TABLE_NAME, 02 CONSTRAINT_COUNT, 03 NUMBER_KEY_FIELDS, 04 PRIMARY_KEY, 05 UNIQUE_CONSTRAINT, 06 ACCESS_PATH_TYPE 07 FROM QSYS2.SYSFILES 08 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 09 AND TABLE_NAME = 'PARENT1'

This statement returns the following result:

SYSTEM_ NUMBER TABLE_ CONSTRAINT _KEY_ PRIMARY UNIQUE_ ACCESS_ NAME _COUNT FIELDS _KEY CONSTRAINT PATH_TYPE ------- ---------- ------ ------- ---------- ------------ PARENT1 1 0 NO NO <NULL>

This show that the PERSON1 Table has a constraint, but no key.

What happens when I insert data into the Table?

01 INSERT INTO PARENT1 VALUES(1,'SMITH','JOHN','1995-09-30')

This statement is successful as there is no row in the Table with the PERSON_ID or '1'.

02 INSERT INTO PARENT1 VALUES(1,'SMITH','JANE','1997-08-09')

This statement errors as there is already a row in the Table with the PERSON_ID of '1'.

SQL State: 23505 Vendor Code: -803 Message: [SQL0803] Duplicate key value specified. Cause . . . . . : A unique index or unique constraint *N in *N exists over one or more columns of table PARENT1 in MYLIB. The operation cannot be performed because one or more values would have produced a duplicate key in the unique index or constraint.

 

Primary key constraint

The primary key constraint performs two purposes. Firstly, it defines the primary key for the Table. Secondly, it provides the unique constraint too.

My example Table with the primary key is:

01 CREATE TABLE MYLIB.PARENT2 ( 02 PARENT_ID INT NOT NULL, 03 LAST_NAME VARCHAR(30) NOT NULL, 04 FIRST_NAME VARCHAR(20) NOT NULL, 05 DATE_OF_BIRTH DATE NOT NULL, 06 PRIMARY KEY (PARENT_ID))

Line 6: This is where I have defined the primary key as being the PARENT_ID.

As I did with PARENT1 I can check the constraint using TABLE_CONSTRAINT:

TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE ---------- --------------------------------- --------------- PARENT2 Q_MYLIB_PARENT2_PARENT_ID_00001 PRIMARY KEY

The CONSTRAINT_TYPE shows that the Table has a primary key.

Checking SYSFILES:

01 SELECT SYSTEM_TABLE_NAME, 02 CONSTRAINT_COUNT, 03 NUMBER_KEY_FIELDS, 04 PRIMARY_KEY, 05 UNIQUE_CONSTRAINT, 06 ACCESS_PATH_TYPE 07 FROM QSYS2.SYSFILES 08 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 09 AND TABLE_NAME = 'PARENT2'

Gives me the following result:

SYSTEM_ NUMBER TABLE_ CONSTRAINT _KEY_ PRIMARY UNIQUE_ ACCESS_ NAME _COUNT FIELDS _KEY CONSTRAINT PATH_TYPE ------- ---------- ------ ------- ---------- ------------ PARENT2 1 1 YES NO KEYED UNIQUE

I can see I have one key field, and the Table has a unique key.

When attempting to insert the same two values in PERSON2, that I did for PERSON1,.../p>

01 INSERT INTO PARENT2 VALUES(1,'SMITH','JOHN','1995-09-30') ; 02 INSERT INTO PARENT2 VALUES(1,'SMITH','JANE','1997-08-09') ;

I get the same error for the second insert statement as the key is the same as the previously inserted row.

SQL State: 23505 Vendor Code: -803 Message: [SQL0803] Duplicate key value specified. Cause . . . . . : A unique index or unique constraint *N in *N exists over one or more columns of table PARENT1 in MYLIB. The operation cannot be performed because one or more values would have produced a duplicate key in the unique index or constraint.

 

Foreign key constraint

The foreign key constraint ensures that the foreign key column that is being inserted or updated in the child Table must exist in the parent Table.

I decided to call my child table CHILD, and it looks like:

01 CREATE TABLE MYLIB.CHILD ( 02 PARENT_ID INT NOT NULL, 03 CHILD_ID INT NOT NULL, 04 LAST_NAME VARCHAR(30) NOT NULL, 05 FIRST_NAME VARCHAR(20) NOT NULL, 06 DATE_OF_BIRTH DATE NOT NULL, 07 PRIMARY KEY (PARENT_ID,CHILD_ID), 08 CONSTRAINT PARENT_ID_EXISTS 09 FOREIGN KEY(PARENT_ID) 10 REFERENCES PARENT2 11 ON DELETE RESTRICT)

Line 2: PARENT_ID is the foreign key.

Line 7: I define a primary key constraint to provide the Table with an appropriate access path, and to ensure that there is a unique constraint too.

Lines 8 – 10: This is the definition that enforces the definition of the foreign key. On line 8 I give this constraint a name, PARENT_ID_EXISTS. I use the FOREIGN KEY on line 9 to give the column name of what will be used as the foreign key. And on line 10 I state within which Table the foreign key is found.

Line 11: This line restricts any delete. Which means I cannot delete a row from the parent file if there are matching rows in the child.

When I use TABLE_CONSTRAINTS to list the constraints on this file...

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

I have two results.

TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE ---------- --------------------------------- --------------- CHILD Q_MYLIB_CHILD_PARENT_ID_00001 PRIMARY KEY CHILD PARENT_ID_EXISTS FOREIGN KEY

The first result is for the primary key. And the second for the foreign key.

When I check this file using SYSFILES I see...

01 SELECT SYSTEM_TABLE_NAME, 02 CONSTRAINT_COUNT, 03 NUMBER_KEY_FIELDS, 04 PRIMARY_KEY, 05 UNIQUE_CONSTRAINT, 06 ACCESS_PATH_TYPE 07 FROM QSYS2.SYSFILES 08 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 09 AND TABLE_NAME LIKE 'CHILD'

This returns:

SYSTEM_ NUMBER TABLE_ CONSTRAINT _KEY_ PRIMARY UNIQUE_ ACCESS_ NAME _COUNT FIELDS _KEY CONSTRAINT PATH_TYPE ------- ---------- ------ ------- ---------- ------------ CHILD 2 2 YES NO KEYED UNIQUE

A result that tells me I have two constraints on this file.

I insert a row of data into CHILD that has the same PARENT_ID as the row in PARENT2.

INSERT INTO CHILD VALUES(1,1,'SMITH','JANE','1997-08-09')

Now I can show referential integrity in action. When I try to delete this row from the parent file:

DELETE FROM PARENT2 WHERE PARENT_ID = 1

As I have the ON DELETE RESTRICT in the child Table it will not allow the delete. Instead, I receive the following error explaining why it cannot.

SQL State: 23001 Vendor Code: -532 Message: [SQL0532] Delete prevented by referential constraint PARENT_ID_EXISTS in MYLIB. Cause . . . . . : Constraint PARENT_ID_EXISTS in MYLIB identifies the table being modified as the parent table in a relationship with dependent table CHILD in MYLIB, with a delete rule of RESTRICT or NO ACTION. The deletion of a row is prevented when there are rows in the dependent table with matching values.

And if I try to insert a row into the child that does not have a matching row in the parent:

INSERT INTO CHILD VALUES(2,1,'JONES','TAMMY','1990-03-16')

I get a different message, as there is not a matching row in the PERSON2 Table.

SQL State: 23503 Vendor Code: -530 Message: [SQL0530] Operation not allowed by referential constraint PARENT_ID_EXISTS in MYLIB. Cause . . . . . : If this is an INSERT, UPDATE, or MERGE statement, the value is not valid for the foreign key because it does not have a matching value in the parent key.

These examples show how easy it is to "push" validation from our programs into the database. This is preferred as the same rules are applied however I manipulate the data in either the parent or child, thereby, ensuring the database will not contain duplicate key rows or orphan children rows.

 

You can learn more about the constraints from the IBM website here.

 

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