How to get the primary key(s) of an IBMi DB2 table via SQL.
Below is some example code on how to pull back the primary keys of a table via SQL. This is useful for when your application needs to update or a delete a unique record that your currently working with (be it in PHP, or another language). Below is the SQL code to retrieve MYTABLE primary keys. To learn more about adding a unique field to an existing table – click here.

WITH xx (CST_NAME, CST_COL_CNT, CST_SCHEMA, CST_TABLE) AS
(
SELECT CONSTRAINT_NAME, CONSTRAINT_KEYS, CONSTRAINT_SCHEMA, TABLE_NAME FROM QSYS2.SYSCST A
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME='MYTABLE'
)
SELECT CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME FROM QSYS2.SYSCSTCOL, xx where
xx.CST_SCHEMA = CONSTRAINT_SCHEMA AND
xx.CST_TABLE = TABLE_NAME AND
xx.CST_NAME = CONSTRAINT_NAME

/* Creating table LIBRARY.MYTABLE */
/* ::NOTE:: PRIMARY KEY (ID) – sets our primary key constraint to the autogenerated ID field*/
CREATE TABLE LIBRARY.MYTABLE (
ID BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO ORDER, NO CYCLE, NO MINVALUE, NO MAXVALUE, CACHE 20) NOT HIDDEN ,
FIRSTNAME CHARACTER (30) NOT HIDDEN ,
LASTNAME CHARACTER (50) NOT HIDDEN ,
PRIMARY KEY (ID) ) NOT VOLATILE ;
/* Setting column labels for LIBRARY.MYTABLE */
LABEL ON COLUMN LIBRARY.MYTABLE ( ID TEXT IS 'Record ID for SQL purposes' );

view raw
TestTable.sql
hosted with ❤ by GitHub

Note: Primary keys can be multiple columns.  Primary keys require uniqueness so the database will not allow duplicate values in the Primary Key(s).

Add/Change Primary Key of Table

On an existing table you can add or change the primary key via
ALTER TABLE MYLIB.MYTABLE ADD PRIMARY KEY (ID) ;
ALTER TABLE MYLIB.MYTABLE DROP PRIMARY KEY  ADD PRIMARY KEY (ID) ;

Find Primary Key of a Keyed Physical File (aka Table in sql)

You may run into  a Keyed physical file.  To find out what keys that file is using you’ll have to run the following command on the 5250 or CL
DSPFD FILE(MYLIB/MYTABLE)
Page Down until you get to Access Path Description and look for the Number of key fields and Key field (s).  Or if you have System I Navigator.  Get to the table -> right click for context menu -> Show Indexes.  In Indexes for LIB.TABLE click F12, and add Key Columns to the view.

I’ve attempted to create a UDTF to get a keyed physical via SQL.  This might need a little work:

Followed Birgitta's article @ https://www.ibm.com/developerworks/ibmi/library/i-power-of-udtf/
CREATE FUNCTION DisplayKeyedPhysicals_Fnc(ParTable VarChar(10))
RETURNS TABLE (FieldWeWant1 Char(10) )
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE CLCmd VarChar(256) Not NULL Default '';
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE ERROR_HIT INTEGER;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET ERROR_HIT = 1;
CREATE TABLE QTEMP.TMPOBJOWN (
FieldWeWant1 Char(10));
END;
SET CLCmd = 'DSPFD FILE(MYLIB/' concat ParTable concat ') '
Concat ' OUTPUT(*OUTFILE) '
Concat ' OUTFILE(QTEMP/TMPOBJOWN) '
Concat ' OUTMBR(*FIRST *REPLACE) ';
CALL QCMDEXC(CLCmd, LENGTH(CLCmd));
RETURN SELECT FieldWeWant1
FROM QTEMP.TMPOBJOWN;
END;
SELECT * FROM TABLE(DisplayKeyedPhysicals_Fnc('MYTABLE')) x;

You ‘ll typically run into this issue if you don’t realized the file is already keyed

SQL State: 23515
Vendor Code: -603
Message: [SQL0603] Unique index cannot be created because of duplicate keys. Cause . . . . . :   An attempt was made to create unique index Q_LIBRARY_TABLE_PKEY1_00001 in LIBRARY or add unique constraint Q_LIBRARY_TABLE_PKEY1_00001 in LIBRARY. The operation cannot be performed because the rows in table TABLE in LIBRARY contain one or more duplicate values in the columns used to create the index. Recovery  . . . :   Do one of the following and try the request again: — Remove the UNIQUE attribute from the CREATE INDEX statement. — Remove the UNIQUE constraint from the ALTER TABLE statement. — Change the data in the related table so that all key values are unique. — Specify UNIQUE WHERE NOT NULL on the CREATE INDEX statement if the duplicate keys contain nulls. The uniqueness restriction would not apply when the key value contains nulls. For information on what rows contain the duplicate key values, see the previously listed messages in the job log.

or

SQL State: 42830
Vendor Code: -538
Message: [SQL0538] The FOREIGN key in constraint Q_LIBRARY_TABLE_KEY1_00001 in LIBRARY not same as the parent key. Cause . . . . . :   The FOREIGN key in constraint Q_LIBRARY_TABLE_KEY1_00001 in LIBRARY is not the same as the parent key of table *N in *N. The FOREIGN key must have the same number of columns as the parent key and the data type, attributes, and field procedure of the FOREIGN key must be identical to the data type, attributes, and field procedure of the corresponding column of the parent key. Recovery  . . . :   Correct the statement so that the description of the FOREIGN key conforms to that of the parent key of the specified table.

Thanks to Scott Forstie for the help w/ finding the primary key