Who this is for: RPG and SQL developers moving table definitions from DDS physical files to SQL DDL, and anyone who has typed CREATE TABLE on IBM i and then been surprised by where the object landed or what its default authority looked like. It assumes you can already write a basic CREATE TABLE statement and want to use keys, constraints, and identity columns correctly.
Verification level: Every statement below is checked against the current IBM i 7.5 CREATE TABLE reference and the Db2 for i constraints topic (see the References section near the end of this article). All SQL examples are IBM DOC CHECKED / STATIC CHECKED — reviewed for syntactic correctness against the documented grammar, not compiled or executed on a live system. No claim is made that any example was run.
Why SQL DDL instead of DDS
Db2 for i still supports DDS-described physical and logical files, but CREATE TABLE and the related SQL DDL statements are IBM’s documented direction for new database objects, and they are the only way to declare SQL-enforced primary key, foreign key, and check constraints directly on the table. A DDS-described physical file can have a unique keyed access path, but referential and check constraints are SQL DDL (or ALTER TABLE) concepts layered on top of the table, confirmed on IBM’s Db2 for i constraints overview.
A basic CREATE TABLE
CREATE TABLE ORDLIB.CUSTMST (
CUSNO DECIMAL(7, 0) NOT NULL,
CUSNAM VARCHAR(40) NOT NULL,
CRDLMT DECIMAL(11, 2) NOT NULL DEFAULT 0,
CUSSTS CHAR(1) NOT NULL DEFAULT 'A'
);
This creates a table with four columns and no keys yet. The next sections add them.
PRIMARY KEY
IBM’s current CREATE TABLE syntax reference (IBM i 7.5, confirmed) documents the primary key as a named table-level constraint:
CREATE TABLE ORDLIB.CUSTMST (
CUSNO DECIMAL(7, 0) NOT NULL,
CUSNAM VARCHAR(40) NOT NULL,
CRDLMT DECIMAL(11, 2) NOT NULL DEFAULT 0,
CUSSTS CHAR(1) NOT NULL DEFAULT 'A',
CONSTRAINT CUSTMST_PK PRIMARY KEY (CUSNO)
);
Two documented facts matter here, both confirmed on IBM’s Db2 for i constraints page:
- A primary key is a special form of a unique constraint. The only documented difference is that a primary key’s columns cannot be nullable.
- “A table can only have one primary key,” per the
CREATE TABLEreference — so if the business key is composite, list every column in the onePRIMARY KEY (...)clause rather than trying to layer a second one.
A primary key can also be written with the column-level shorthand CONSTRAINT constraint-name PRIMARY KEY directly after a single column’s definition, but the table-level form above is clearer once you add a foreign key referencing it, so this article uses the table-level form throughout.
CREATE INDEX is not a constraint. IBM’s constraints page is explicit that a unique index created with CREATE INDEX enforces uniqueness at the storage level but “such an index is not a constraint” — it does not appear in the SQL constraint catalogs, and it is not what a REFERENCES clause on another table points to. If you need a primary key or unique constraint (for example, as the target of a foreign key), declare it as a constraint, not only as a unique index.
FOREIGN KEY
A child table references the parent’s primary (or unique) key with a table-level FOREIGN KEY ... REFERENCES constraint:
CREATE TABLE ORDLIB.ORDMST (
ORDNO DECIMAL(9, 0) NOT NULL,
CUSNO DECIMAL(7, 0) NOT NULL,
ORDDAT DATE NOT NULL,
ORDAMT DECIMAL(11, 2) NOT NULL,
ORDSTS CHAR(1) NOT NULL DEFAULT 'O',
CONSTRAINT ORDMST_PK PRIMARY KEY (ORDNO),
CONSTRAINT ORDMST_CUS_FK FOREIGN KEY (CUSNO)
REFERENCES ORDLIB.CUSTMST (CUSNO)
ON DELETE RESTRICT
ON UPDATE NO ACTION
);
Per IBM’s documented grammar for the references-clause, ON DELETE and ON UPDATE each accept NO ACTION, RESTRICT, CASCADE, SET NULL, or SET DEFAULT for ON DELETE, and NO ACTION or RESTRICT for ON UPDATE — and “the ON DELETE and ON UPDATE clauses may be specified in either order.” If you omit them, the documented default behavior is NO ACTION.
The referential rule itself, confirmed verbatim on IBM’s constraints page: foreign key values “are valid only if” they match a parent key value, “or [if] some component of the foreign key is null.” That second clause matters in practice — a nullable foreign key column with a null value never violates the constraint, no matter what is or is not in the parent table. If the business rule requires every order to have a real customer, make CUSNO NOT NULL as shown above; the foreign key constraint alone does not forbid nulls.
Enforcement timing. IBM’s constraints page states referential constraints are enforced “during the execution of INSERT, UPDATE, and DELETE statements” — DELETE is in that list because deleting a parent row can violate a child’s foreign key, which is exactly what ON DELETE RESTRICT/CASCADE/SET NULL controls.
CHECK constraints
A CHECK constraint restricts the values a column (or a row) may take:
ALTER TABLE ORDLIB.ORDMST
ADD CONSTRAINT ORDMST_AMT_CK CHECK (ORDAMT >= 0);
ALTER TABLE ORDLIB.ORDMST
ADD CONSTRAINT ORDMST_STS_CK CHECK (ORDSTS IN ('O', 'H', 'C', 'X'));
IBM’s constraints page confirms check constraints are enforced “during the execution of INSERT and UPDATE statements” (not DELETE — a check constraint only ever looks at the row being written), and that for the row to pass, the condition must evaluate to “TRUE or unknown (because of a null value).” That second part is easy to miss: a CHECK (ORDAMT >= 0) constraint does not reject a null ORDAMT — NULL >= 0 evaluates to unknown, not false, and unknown passes. If a column must have both a real value and a value within range, make it NOT NULL as well as adding the CHECK.
Db2 for i’s documented CREATE TABLE grammar also allows ON INSERT VIOLATION SET column = DEFAULT and ON UPDATE VIOLATION PRESERVE column clauses on a check constraint — rather than rejecting the statement outright, these quietly substitute the column’s default value (on insert) or keep its existing value (on update) when the check would otherwise fail. IBM’s syntax reference notes that if you use either clause, “column C must be referenced in these clauses” — i.e. that clause applies to the specific column named, not to the row as a whole. This is a deliberate design choice (silently normalizing bad input) and not appropriate for every check; most teams should let the default (statement rejected) behavior stand unless there’s a specific reason to substitute a value instead.
GENERATED ALWAYS AS IDENTITY
An identity column is Db2 for i’s auto-numbering mechanism, declared on the column itself:
CREATE TABLE ORDLIB.AUDITLOG (
LOG_ID BIGINT GENERATED ALWAYS AS IDENTITY
(START WITH 1 INCREMENT BY 1 NO CYCLE CACHE 20 NO ORDER),
EVENT_TS TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
EVENT_TEXT VARCHAR(500) NOT NULL,
CONSTRAINT AUDITLOG_PK PRIMARY KEY (LOG_ID)
);
Confirmed on IBM’s CREATE TABLE reference, the full documented clause is:
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY (
START WITH numeric-constant
INCREMENT BY numeric-constant
{ NO MINVALUE | MINVALUE numeric-constant }
{ NO MAXVALUE | MAXVALUE numeric-constant }
{ NO CYCLE | CYCLE }
{ NO CACHE | CACHE integer }
{ NO ORDER | ORDER }
)
Documented defaults (confirmed): START WITH 1, INCREMENT BY 1, NO CYCLE, CACHE 20, NO ORDER. Two more documented points worth knowing before you pick ALWAYS vs BY DEFAULT:
- IBM’s own wording recommends
ALWAYS: “ALWAYS is the recommended value.” WithGENERATED ALWAYS, the database always supplies the value and an application-supplied value in a plainINSERTis rejected (anOVERRIDING SYSTEM VALUEclause exists for the cases that genuinely need to supply one, such as a one-time data load). - “An identity column is implicitly NOT NULL,” per the same page — you do not need to (and should not need to) add
NOT NULLseparately.
GENERATED BY DEFAULT AS IDENTITY is the escape hatch for migrations: it lets the application supply a value (for example, when loading historical rows with their original keys) while still generating one automatically when none is given. A user-submitted IBM Ideas report notes that some IBM i data-loading tools (such as Optim) historically could not load directly into a GENERATED ALWAYS column, and that the workaround was to switch the column to GENERATED BY DEFAULT temporarily, load the data, then switch it back. That report describes a product limitation rather than documented SQL behavior, so confirm whether it still applies to your release and tooling before relying on it — test your own load path first.
System naming vs SQL naming — why it matters for DDL
Db2 for i supports two naming conventions, and which one is active changes how your CREATE TABLE statement’s unqualified names resolve, confirmed on IBM’s SQL and system naming conventions page:
- System naming (
*SYS): object references accept either a slash or a dot —schema/tableorschema.table— and unqualified names resolve the traditional IBM i way, through the job’s library list. - SQL naming (
*SQL): object references use the dot form only —schema.table— and, per the same page, SQL naming “has no concept of a library list.” An unqualified name instead resolves against the connection’s default schema (the SQL term for a library in this context), which for embedded static SQL is set with theDFTRDBCOLparameter on the compile command or with aSET OPTIONstatement, and for an interactive or ODBC/JDBC session is whatever schema the connection established as current.
The naming mode is set with a parameter on the SQL interface you’re using (for example, NAMING(*SYS) or NAMING(*SQL) on RUNSQLSTM/CRTSQLxxx) or with SET OPTION. This is not a cosmetic choice:
- A
CREATE TABLE ORDMST (...)with no schema qualifier lands in a different place depending on naming mode and session state — the current library on the library list under system naming, or the connection’s default schema under SQL naming. Teams that see “it worked from my SQL session but the RPG program can’t see the table” are usually hitting exactly this. - IBM’s naming page also notes that the naming mode affects more than the separator: “there are many more differences, especially with regard to access authority and ownership of database objects” between the two modes. This article does not attempt to enumerate every authority difference — check the authority behavior for your own
NAMINGsetting before assuming it matches system-naming defaults you may be used to from DDS-era development.
Practical rule: fully qualify every CREATE TABLE, ALTER TABLE, and REFERENCES target with an explicit schema/library name in DDL scripts that may run under either naming mode, rather than relying on the current library or default schema to be set correctly. It costs nothing to type and removes an entire category of “created in the wrong place” defects.
A complete example
Putting the pieces together — a parent/child pair with a primary key, a foreign key, a check constraint, and an identity surrogate key:
CREATE TABLE ORDLIB.CUSTMST (
CUSNO DECIMAL(7, 0) NOT NULL,
CUSNAM VARCHAR(40) NOT NULL,
CRDLMT DECIMAL(11, 2) NOT NULL DEFAULT 0,
CUSSTS CHAR(1) NOT NULL DEFAULT 'A',
CONSTRAINT CUSTMST_PK PRIMARY KEY (CUSNO),
CONSTRAINT CUSTMST_STS_CK CHECK (CUSSTS IN ('A', 'I'))
);
CREATE TABLE ORDLIB.ORDMST (
ORDNO BIGINT GENERATED ALWAYS AS IDENTITY
(START WITH 1000 INCREMENT BY 1 NO CYCLE CACHE 20 NO ORDER),
CUSNO DECIMAL(7, 0) NOT NULL,
ORDDAT DATE NOT NULL DEFAULT CURRENT_DATE,
ORDAMT DECIMAL(11, 2) NOT NULL,
ORDSTS CHAR(1) NOT NULL DEFAULT 'O',
CONSTRAINT ORDMST_PK PRIMARY KEY (ORDNO),
CONSTRAINT ORDMST_CUS_FK FOREIGN KEY (CUSNO)
REFERENCES ORDLIB.CUSTMST (CUSNO)
ON DELETE RESTRICT
ON UPDATE NO ACTION,
CONSTRAINT ORDMST_AMT_CK CHECK (ORDAMT >= 0),
CONSTRAINT ORDMST_STS_CK CHECK (ORDSTS IN ('O', 'H', 'C', 'X'))
);
Common mistakes
- Relying on a unique index where a constraint is needed. A
CREATE INDEX ... UNIQUEenforces uniqueness but is not itself a constraint and cannot be the documented target of aREFERENCESclause in the same sense a declaredPRIMARY KEY/UNIQUEconstraint is. - Assuming a CHECK constraint rejects nulls. It does not, unless the column is also
NOT NULL. A condition that evaluates to unknown passes. - Forgetting
ON DELETE/ON UPDATEand gettingNO ACTIONby default when the business rule actually needsRESTRICT,CASCADE, orSET NULL. Decide the rule explicitly rather than accepting the default silently. - Leaving
CREATE TABLEunqualified and letting the current naming mode and library list or default schema decide where the object lands — fine for ad hoc testing, risky in a script meant to run the same way in every environment. - Choosing
GENERATED BY DEFAULTout of habit. IBM’s own documentation recommendsALWAYSfor new identity columns; reach forBY DEFAULTonly when you have a specific, known need (typically a one-time data migration) to supply the value yourself.
FAQ
Can a table have more than one primary key?
No. IBM’s CREATE TABLE reference states a table can only have one primary key. If several columns together form the business key, put them all in the one PRIMARY KEY (...) clause.
Does a foreign key reject a null value?
No. Per IBM’s documented rule, a foreign key value is valid if it matches a parent key value or if some component of the foreign key is null. Make the column NOT NULL separately if nulls should not be allowed.
Is a unique index the same as a unique constraint?
No. IBM’s constraints documentation states that an index created with CREATE INDEX is not a constraint, even if it is declared UNIQUE. Use a CONSTRAINT ... UNIQUE or CONSTRAINT ... PRIMARY KEY clause on the table when you need an SQL constraint.
Should I use GENERATED ALWAYS or GENERATED BY DEFAULT for a new surrogate key?
IBM’s documentation recommends ALWAYS for new columns. Use BY DEFAULT only for the specific cases (commonly a data migration) that genuinely need to supply the value themselves.
References
- IBM i 7.5:
CREATE TABLEstatement — the table-level and column-levelPRIMARY KEY,FOREIGN KEY/REFERENCES(withON DELETE/ON UPDATEoption lists), andCHECKconstraint clauses; the fullGENERATED { ALWAYS | BY DEFAULT } AS IDENTITYclause and its documented defaults; the “ALWAYS is the recommended value” and “implicitly NOT NULL” statements; “a table can only have one primary key.” - Db2 for i: constraints overview — the unique-constraint definition and its INSERT/UPDATE enforcement; that a primary key is a unique constraint whose only documented difference is non-nullable columns; that a
CREATE INDEXunique index “is not a constraint”; the referential-constraint rule and its INSERT/UPDATE/DELETE enforcement; the check-constraint rule (TRUE or unknown passes) and its INSERT/UPDATE enforcement. - Db2 for i: SQL and system naming conventions — how system naming resolves unqualified names via the library list, how SQL naming resolves them against the default schema instead, how the naming mode is set, and the note that the two modes also differ in access authority and object ownership (not fully enumerated on that page).
Two details called out above are not independently confirmed beyond what’s quoted from these sources: the exact release at which some IBM i data-loading tools’ historical GENERATED ALWAYS limitation was fixed or worked around, and the full list of authority/ownership differences between system and SQL naming. Verify both against your own release before relying on them.