Basic constraints with DDS files

In my previous post I described how I could add several constraints to DDL Tables. Here I am going to describe how I can do the same with DDS physical files.

I will have two sets of parent and child files. The first I will add the constraints using SQL statements. The second I will use the Add Physical File Constraint command, ADDPFCST.

I will be adding the following constraints to the physical files:

1. Add primary key constraint using SQL
2. Add primary key constraint using CL command
3. Foreign key constraint using SQL
4. Foreign key constraint using CL command

 

Add primary key constraint using SQL

In my examples where I will be using the parent physical file PARENT1 and the child physical file CHILD1. I have tried to keep these physical files as similar as I could do to the DDL tables I used in my previous post.

The DDS source for PARENT1 is:

01 A UNIQUE 02 A R PARENT1R 03 A PARENT_ID 9P 0 04 A LAST_NAME 30A VARLEN 05 A FIRST_NAME 20A VARLEN 06 A BIRTH_DATE L 07 A K PARENT_ID

Line 1: To add the primary key constraint the file must be coded to have a unique key. The "uniqueness" is achieved by this UNIQUE keyword.

Lines 4 and 5: Both of these lines have the VARLEN keyword, which gives them both the same attributes as a VARCHAR in a DDL table.

Line 7: I need to have a key for the file. Combined with the UNIQUE keyword gives it a unique but not a unique constraint.

To add the primary key I use the Alter Table SQL statement:

01 ALTER TABLE PARENT1 ADD PRIMARY KEY(PARENT_ID)

The primary key constraints adds both a primary key and enforces the need for a unique key.

I use the TABLE_CONSTRAINTS View to show that the constraint was added:

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'

Which returns the following result:

TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE ---------- --------------------------------- --------------- PARENT1 Q_MYLIB_PARENT1_PARENT_ID_00001 PRIMARY KEY

Which shows I have the primary key constraint in place on PERSON1.

 

Add primary key constraint using CL command

The parent and child physical files I will be adding the constraints using the ADDPFCST command are: PARENT2 and CHILD2.

The DDS source for PARENT2 is like PERSON1, the only difference is the record format name:

01 A UNIQUE 02 A R PARENT2R 03 A PARENT_ID 9P 0 04 A LAST_NAME 30A VARLEN 05 A FIRST_NAME 20A VARLEN 06 A BIRTH_DATE L 07 A K PARENT_ID

Lines 1 and 7: For the primary key constraint I must have the UNIQUE keyword, line 1, and a key, line 7.

I am using the ADDPFCST CL command to add the constraint:

01 ADDPFCST FILE(PARENT2) TYPE(*PRIKEY) KEY(PARENT_ID)

There is an additional command parameter I could have used, "Constraint name". I chose not to, and as its default is '*GEN' the constraint's name is automatically generated.

To view this constraint I use the TABLES_CONSTRAINT View again:

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

Which returns the result for PARENT2:

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

 

Foreign key constraint using SQL

The DDS code for the first child physical file, CHILD1, looks like:

01 A REF(PARENT1) 02 A UNIQUE 03 A R CHILD1R 04 A PARENT_ID R 05 A CHILD_ID R REFFLD(PARENT_ID *SRC) 06 A LAST_NAME R 07 A FIRST_NAMER 08 A BIRTH_DATER 09 A K PARENT_ID 10 A K CHILD_ID

Line 1: One of the things about creating DDS files that has always impressed me is the ability to use reference files. For this file I am using the PARENT1 as the reference for definition for all of the fields in the file.

Line 2: This file will need a primary constraint, therefore, its key needs to be unique.

Line 5: I am defining CHILD_ID field to have the same definition as the PARENT_ID field.

I need to add two constraints to this file: primary key and foreign key constraints. Fortunately, I can do this in one SQL Alter Table statement:

01 ALTER TABLE CHILD1 02 ADD PRIMARY KEY(PARENT_ID,CHILD_ID) 03 ADD FOREIGN KEY(PARENT_ID) REFERENCES PARENT1 04 ON DELETE RESTRICT

Line 2: Adds the primary key constraint.

Lines 3 and 4: Add the foreign key constraint, with the delete restriction that prevents records from PARENT1 from being deleted if there are records with the matching key in CHILD1.

I can show the constraints for CHILD1:

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

Both constraints are returned in the results:

TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE ---------- --------------------------------- --------------- CHILD1 Q_U4142SH_CHILD1_PARENT_ID_00001 PRIMARY KEY CHILD1 Q_U4142SH_CHILD1_PARENT_ID_00002 FOREIGN KEY

 

Add primary key constraint using CL command

PARENT2 will have a child physical file CHILD2. Its DDS looks like:

01 A UNIQUE 02 A R PARENT2R 03 A PARENT_ID 9P 0 04 A LAST_NAME 30A VARLEN 05 A FIRST_NAME 20A VARLEN 06 A BIRTH_DATE L 07 A K PARENT_ID

I am not going to describe this source as it is the same as CHILD1, apart from the record format name.

To add the primary key and foreign key constraints I need to use the ADDPFCST command twice:

01 ADDPFCST FILE(CHILD2) TYPE(*PRIKEY) KEY(PARENT_ID CHILD_ID) 02 ADDPFCST FILE(CHILD2) TYPE(*REFCST) KEY(PARENT_ID) PRNFILE(PARENT2) + DLTRULE(*RESTRICT)

Line 1: This adds the primary key constraint.

Line 2: This adds the foreign key constraint with a restricted delete.

I can show that these constraints were added using TABLE_CONSTRAINTS again:

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

Two results are returned showing that the constraints are now in place.

TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE ---------- --------------------------------- --------------- CHILD2 Q_U4142SH_CHILD2_PARENT_ID_00001 PRIMARY KEY CHILD2 Q_U4142SH_CHILD2_PARENT_ID_00002 FOREIGN KEY

 

Now for testing. I need to add records to all the files:

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

I then try to delete the record from the parent files:

01 DELETE FROM PARENT1 WHERE PARENT_ID = 1 ; 02 DELETE FROM PARENT2 WHERE PARENT_ID = 1 ;

Both statements return the same error informing me that I cannot delete the record from the parent as there are matching records in the child.

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.

The other test I performed with the DDL tables was to try to add to the children where there is not a matching record in the parent.

01 INSERT INTO CHILD1 VALUES(2,1,'JONES','TAMMY','1990-03-16') ; 02 INSERT INTO CHILD2 VALUES(2,1,'JONES','TAMMY','1990-03-16') ;

Both of the above statements produce the same error:

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.

The techniques described show how you can add constraints to physical files. It is your choice whether you want to use SQL's Alter table or the ADDPFCST command.

 

You can learn more about the Add Physical File Constraints CL command from the IBM website here.

 

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