SQLCODE -538: Foreign key doesn't match parent key

What it means

A referential constraint links a foreign key in a child table to a parent key in another table. You define it in CREATE TABLE or ALTER TABLE with FOREIGN KEY (cols) REFERENCES parent (cols). If REFERENCES lists no columns, Db2 uses the parent's primary key.

Db2 checks two things before it accepts the constraint. The parent columns must be the primary key or a unique key of the parent table. And the foreign key must describe the same key, column for column, in order. That means the same number of columns, the same data types, the same lengths and the same field procedures. If either check fails, Db2 rejects the statement with -538.

For temporal tables there's one more rule. PERIOD BUSINESS_TIME has to appear on both keys or on neither, and the period must mean the same thing in both tables.

The token in the message is the constraint name. If the FOREIGN KEY clause had no constraint name, Db2 shows the first column of the foreign key instead.

Common causes

• The REFERENCES clause points at columns that aren't a primary key or unique key of the parent, for example a code column that only has a non-unique index.
• The data types differ, such as INTEGER in the child and DECIMAL(9,0) in the parent, or CHAR(8) against CHAR(10), or CHAR against VARCHAR.
• The columns are listed in a different order from the parent key, so the first child column is compared with the wrong parent column.
• The foreign key has more or fewer columns than the parent key.
• One column uses a field procedure and the other doesn't.
• PERIOD BUSINESS_TIME is on one key and not the other.

How to find the problem

1. Take the constraint or column name from the message and find the FOREIGN KEY clause in the DDL.
2. List the parent's keys. SELECT CONSTNAME, TYPE FROM SYSIBM.SYSTABCONST WHERE TBCREATOR = 'PROD' AND TBNAME = 'CUSTOMER' shows primary (P) and unique (U) constraints, and SYSIBM.SYSKEYCOLUSE lists their columns in order. Columns with KEYSEQ greater than zero in SYSIBM.SYSCOLUMNS make up the primary key.
3. Compare COLTYPE, LENGTH and SCALE in SYSIBM.SYSCOLUMNS for each pair of columns, child against parent, in key order.

How to fix it

• Point REFERENCES at the parent's primary key or at a unique key, and list the columns in the same order as that key.
• If the parent columns should be a key but aren't, have the DBA create the unique index and add a UNIQUE constraint on them first.
• Change the child column definitions so their types and lengths match the parent exactly. On a table that already holds data that may mean ALTER COLUMN SET DATA TYPE, or re-creating the table.
• Keep key columns defined from one shared copy of the data model so the child and parent can't drift apart.

What it looks like

DSNT408I SQLCODE = -538, ERROR:  FOREIGN KEY ORDCUSTFK DOES NOT CONFORM TO THE DESCRIPTION OF A PARENT KEY OF TABLE PROD.CUSTOMER
DSNT418I SQLSTATE   = 42830 SQLSTATE RETURN CODE

Technical reviewer: Albert Joseph, WND Software.

Last reviewed 2026-10-18 by the WND Software team. Written in our own words. For the full reference, check IBM’s Db2 for z/OS documentation for your release.