Course contents
On this page
Give each product a row and an identity
Use a small product table to understand columns, types, keys, and constraints.
We need to remember the same facts for every product: its number, name, price, and stock. A table groups these records into a common shape.
| product_id | name | price | stock |
|---|---|---|---|
| 12 | Notebook | 8 | 4 |
| 27 | Canvas tote | 20 | 1 |
| 46 | Desk lamp | 35 | 2 |
Each row describes one product. Each column describes one kind of fact. The value 20 sits in the price column of product 27’s row. Prices in this example are whole credits so that money rounding does not distract from the table.
A row needs an identity
The shop might sell two different products called “Notebook.” A name alone would then be an ambiguous way to select one. We assign each product a distinct number and use it whenever we refer to that product.
The table’s primary key is the column, or combination of columns, chosen to identify each row. Here it is product_id. A primary key cannot be missing or duplicated. Product 27 remains product 27 even if its name or price changes.
The number is not a storage position. Product 27 need not be the twenty-seventh row. When we later rearrange records or build indexes, its identity should stay the same.
Before a price change
ID: 27
Name: Canvas tote
Price: 20
After a price change
ID: 27
Name: Canvas tote
Price: 25
Describe what values are allowed
A type says what kind of value a column can contain. Our product number and stock use integers, which are whole numbers. Names use text. A constraint adds a rule, such as “stock cannot be negative.”
Together, the column definitions and rules form the table’s schema. They give the engine a way to reject a row that does not fit. For this example, the schema requires a distinct product number, a name, a nonnegative whole-credit price, and nonnegative stock.
Some tables permit a missing value, represented in SQL by NULL. Missing is different from zero: zero stock says the count is known to be zero. Missing stock says we do not have a count. Our shop requires a known stock count, so its schema rejects NULL there.
Test the rules
Suppose we try to add another row with ID 27, name “Travel bag,” price 40, and stock 3. Every value has the right type. Should the engine accept it?
Check identity as well as types
No. ID 27 already identifies the canvas tote. Accepting another row with that ID would violate the primary-key rule. Correct types are necessary, but they do not establish that every table rule is satisfied.
The engine should report the conflict so the caller can choose an unused ID or deliberately update the existing product.
We have a shape for our data and rules for valid rows. Next we need a language for asking the engine to read and change them.
Further reading
PostgreSQL’s constraint documentation explains primary keys, missing values, and checks in a real engine. It is a reference for the rules introduced here; you do not need its advanced sections yet.