Tag Archives: primary key

How to get a table’s primary key(s) from IBMi DB2 via SQL

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.

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:

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

Advertisements