# SQLCODE -542: Key column allows nulls

Source: https://wndsoftware.com/mainframe-errors/sqlcode-542

### SQLCODE -542: Key column allows nulls

SQLCODE -542 (SQLSTATE 42831) means a column that allows nulls was used in a primary key, a unique constraint or a parent key named in REFERENCES. Db2 needs every column of these keys to be defined NOT NULL.


#### Other names for this code

Also written as `SQL -542`, `SQLCODE=-542`, `SQLCODE = -542`, `db2 -542`, `sqlstate 42831`, `SQL0542N`, `cannot be a column of a primary key because it can contain null values`.


### What it means

A key that identifies a row has to have a value in every column. So Db2 requires each column of a primary key, of a UNIQUE constraint and of a parent key referenced by a foreign key to be defined NOT NULL. Db2 for z/OS also applies the rule to the HASH KEY of a hash-organized table, a Db2 10 feature that Db2 12 deprecated.

In Db2, a column allows nulls unless you code NOT NULL. Leaving it off is the usual reason for -542. The statement is rejected and nothing is created or changed. The message names the column.

A unique index is different from a unique constraint. `CREATE UNIQUE WHERE NOT NULL INDEX` can be built on nullable columns. That index doesn't make the columns a key that a foreign key can reference, though.


### Common causes

- CREATE TABLE lists a column in PRIMARY KEY or UNIQUE without NOT NULL on its definition.
- ALTER TABLE ADD PRIMARY KEY or ADD UNIQUE names a column that was created nullable.
- DDL was converted from a database that makes primary key columns NOT NULL automatically. Oracle, SQL Server and MySQL do, and Db2 doesn't.
- A REFERENCES clause names parent columns that allow nulls.


### How to find the problem

- Take the column name from the message.
- Check its nullability with `SELECT NAME, NULLS FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR = 'PROD' AND TBNAME = 'CUSTOMER'`. NULLS = 'Y' means the column allows nulls.
- For a REFERENCES error, run the same query against the parent table.
- If the table already has data, count the rows where the column is null before you plan a change.


### How to fix it

- For a new table, add NOT NULL to every key column in the CREATE TABLE and run it again.
- For an existing table, Db2 for z/OS doesn't offer a simple ALTER that turns a nullable column into NOT NULL. The usual route is to unload the data, drop the table, re-create it with NOT NULL on the key columns and reload. IBM gives the same advice for a parent table named in REFERENCES.
- Before reloading, replace any nulls in the key columns with real values, or the reload will fail.
- When converting DDL from another database, add NOT NULL explicitly to every primary key column.


### IBM documentation

[IBM documentation: -542](https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=codes-542). Use the documentation for your installed release.

[Report a correction to WND Software](https://wndsoftware.com/contact#message).

Technical reviewer: [Albert Joseph](https://wndsoftware.com/authors/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.


### Related codes

[SQLCODE -538 Foreign key doesn't match parent key Read →](https://wndsoftware.com/mainframe-errors/sqlcode-538)
[SQLCODE -540 Table definition incomplete, missing index Read →](https://wndsoftware.com/mainframe-errors/sqlcode-540)
[SQLCODE -603 Duplicate rows block a unique index Read →](https://wndsoftware.com/mainframe-errors/sqlcode-603)
[SQLCODE -624 Table already has a primary key Read →](https://wndsoftware.com/mainframe-errors/sqlcode-624)
[SQLCODE -407 Null value in a NOT NULL column Read →](https://wndsoftware.com/mainframe-errors/sqlcode-407)

### Questions


#### Why doesn't Db2 make primary key columns NOT NULL for me?

Db2 expects the column definition to say NOT NULL itself and rejects the key if it doesn't. Some other databases add NOT NULL silently, which is why converted DDL often hits -542.


#### Is -542 the same on Db2 for LUW?

Yes. Db2 for LUW reports SQL0542N with SQLSTATE 42831. LUW does let you change an existing column with ALTER COLUMN SET NOT NULL, which usually leaves the table needing a reorg.


#### Can I have a unique index on a nullable column instead?

Yes. CREATE UNIQUE WHERE NOT NULL INDEX allows it, and repeated nulls are then allowed. It isn't a primary key or unique constraint, so a foreign key can't reference it.

[Db2 SQLCODEs](https://wndsoftware.com/mainframe-errors/db2-sqlcodes) · [SQLCODE -540](https://wndsoftware.com/mainframe-errors/sqlcode-540) · [SQLCODE -543](https://wndsoftware.com/mainframe-errors/sqlcode-543)

[← All error codes](https://wndsoftware.com/mainframe-errors/)