Primary key
The column that makes every row in a table different from every other.
Also written key, foreign key, unique key
Why it matters. Without a key, the same account could be added twice. A primary key makes Db2 refuse the second one.
See it
NOT NULL means the column must always have a value.
Syntax
CREATE TABLE schema.table (column type [NOT NULL], ..., PRIMARY KEY (column [, column ...]))
- PRIMARY KEY
- The column or columns that identify a row. Each must be NOT NULL.
- NOT NULL
- The column must always have a value.
- UNIQUE
- Another column set that can't repeat, but isn't the key.
- FOREIGN KEY ... REFERENCES
- A column that must match a key in another table.
Try it PRIMARY KEY
- 1
You're in the SYSIN lines of job PKTEST, a DSNTEP2 step. Write the statement that makes a small table of fee codes, M1B.FEECODES. CODE is 4 characters and FEE is 5 digits with 2 decimals. Neither may be empty. CODE is the primary key.
SYSIN CREATE TABLE M1B.FEECODES (CODE CHAR(4) NOT NULL, FEE DECIMAL(5,2) NOT NULL, PRIMARY KEY (CODE)); - 2
Add the statement for the monthly fee: code MNTH, fee 5.00.
SYSIN INSERT INTO M1B.FEECODES VALUES ('MNTH', 5.00); - 3
Add one more statement: MNTH again, with a different fee, 9.00.
SYSIN INSERT INTO M1B.FEECODES VALUES ('MNTH', 9.00); - 4
Submit the job PKTEST from your DEV JCL library.
SUBMIT 'ZB0001.DEV.JCL(PKTEST)' - 5
Read the output of the job. Find what happened to the third statement.
OUTPUT PKTEST
Remember
- A primary key makes every row unique.
- Db2 refuses a second row with the same key. The SQLCODE is -803, with SQLSTATE 23505.
- Choose a value that never repeats by design, like an account number.
The usual mistake
PRIMARY KEY (SURNAME)
Two customers can share a surname. The second one is refused, or you have to change the key later.
PRIMARY KEY (ACCTNO), a value that is unique by design
On real z/OS
On real Db2 for z/OS, a primary key needs a unique index on its columns. The table is not complete until the index exists, unless Db2 creates it for you, which it does for tables in implicitly created table spaces. MF/OS enforces the key without an index.
Where can I practise Primary key?
Right here, at ZedBank. You take a junior job at a simulated bank and use Primary key in real work, with Zed beside you. There's nothing to install.
Start at ZedBankRelated
- IndexA structure that lets a database find rows by value without reading the whole table.
- SQLThe language used to query and change relational data. The same SQL used everywhere else, running against mainframe tables.
- DB2 for z/OSIBM's relational database on the mainframe, where a great deal of the world's banking and insurance data is stored.
- KSDSThe most common VSAM dataset type, where every record has a unique key used to find it directly.
- VSAMA family of dataset types built for fast record access by key, used where applications need to look records up rather than read them in order.