Home
AS400 to IBM i
Db2

Primary Key in DDS and DDL tables

Primary Key in DDS and DDL tables
Primary Key in DDS and DDL tables, ADDPFCST, PRIMAR KEY CONSTRAINT in TABLE iN IBMi AS400
Primary Key in DDS and DDL tables
Key in the table means the way in which records are fetched from the table. We can have multiple records with a single key or can have only one record per key (Unique keys & Primary Key)

Actually what is this primary key and why we are using them

The primary key is a type of unique key and it is primarily used to identify the rows in the table. The primary key can be a single field or the combination of multiple fields, these combined field keys are known as composite keys

So, in this article, we will see how we can add primary key constraints in DDS and DDL file 

DDS table

Below is the DDS table code on which primary key constraint will be applied
A          R RDDS2                                
A            FLD1          10A                    
A            FLD2          20A         VARLEN     
A            FLD3          15G         CCSID(1200)
A            FLD4          30A         ALWNULL    
A            FLD5           2P 0                  
A            FLD6           2S 0

After creating the file object for above mention code.
Now, by using the ADDPFCST command we will add the primary key constraint to the above mentioned DDS file

ADDPFCST Command

ADDPFCST FILE(LIBRARY/DDS_FILE)     
         TYPE(*PRIKEY)              
         KEY(FLD1)                  
         CST(Primary_Key_Constraint)
By using the ADDPFCST command not only primary key but we can add another constraint also:
  • *REFCST : Referential constraint
  • *UNQCST : Unique constraint
  • *PRIKEY : Primary key constraint
  • *CHKCST : Check constraint
Using the DSPFD command on the DDS file now we can see that primary key constraint has been added

(DDS File) DSPFD Command

Unique key values required  . . . . . . . . : UNIQUE     Yes       
Access path journaled . . . . . . . . . . . :            No        
Access path . . . . . . . . . . . . . . . . :            Keyed     
Constraint Type . . . . . . . . . . . . . . :            PRIMARY   
Number of key fields  . . . . . . . . . . . :              1       
Record format . . . . . . . . . . . . . . . :            RDDS2     
  Key field . . . . . . . . . . . . . . . . :            FLD1      
    Sequence  . . . . . . . . . . . . . . . :            Ascending 
    Sign specified  . . . . . . . . . . . . :            UNSIGNED  
    Zone/digit specified  . . . . . . . . . :            *NONE     
    Alternative collating sequence  . . . . :            No

DDL Table

Below is the DDL table structure which is having the primary key constraint in it.
CREATE TABLE DDL2 (                              
  FLD1 CHAR(10) NOT NULL WITH DEFAULT,           
  PRIMARY KEY(FLD1),                             
  FLD2 VARCHAR(20) NOT NULL WITH DEFAULT,        
  FLD3 GRAPHIC NOT NULL WITH DEFAULT CCSID 1200, 
  FLD4 CHAR(30),                                 
  FLD5 DECIMAL(2) NOT NULL WITH DEFAULT,         
  FLD6 NUMERIC(2) NOT NULL WITH DEFAULT          
                  )                              
                  RCDFMT RDDS2;
Execute the RUNSQLSTM command to create the object for the script

RUNSQLSTM Command (Run SQL Statements)

RUNSQLSTM SRCFILE(LIBRARY/QSQLSRC)
          SRCMBR(DDS_SCRIPT)      
          COMMIT(*NONE)
No command is needed, just run the above mentioned DDL script using the RUNSQLSTM command and primary key constraint will be generated for the field FLD1

(DDL  Table) DSPFD Command

Unique key values required  . . . . . . . . : UNIQUE     Yes       
Access path journaled . . . . . . . . . . . :            No        
Access path . . . . . . . . . . . . . . . . :            Keyed     
Constraint Type . . . . . . . . . . . . . . :            PRIMARY   
Number of key fields  . . . . . . . . . . . :              1       
Record format . . . . . . . . . . . . . . . :            RDDS2     
  Key field . . . . . . . . . . . . . . . . :            FLD1      
    Sequence  . . . . . . . . . . . . . . . :            Ascending 
    Sign specified  . . . . . . . . . . . . :            UNSIGNED  
    Zone/digit specified  . . . . . . . . . :            *NONE     
    Alternative collating sequence  . . . . :            No

Related Post

Read also :

Post a Comment

© ‧ AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain