SQL 3 🛢️ Tables, Rows, Columns, and Keys

You have seen the relational model and the software that implements it. This chapter is about the structure itself: the table, the row, the column, and the keys that connect them. These are the primitives that every SQL statement operates on. Every SELECT, every INSERT, every JOIN works because the table has columns, the columns have types, and the keys define how the tables relate.

The previous chapters introduced these concepts at a high level. This chapter goes into the detail: the exact definition of each term, the constraints that can be applied to columns, the different kinds of keys, and the rules that determine whether a table design is sound. The LFCA and Kubernetes chapters in this series covered infrastructure. This chapter covers the data structure that sits underneath every application.

Key point: A table is not just a grid of data. It is a relation — a set of rows where each row is a tuple and each column is an attribute with a defined domain. The table has a schema that specifies the column names, the data types, and the constraints. The schema is the contract between the application and the database. The data is what fills the table. The keys are the rules that keep the data consistent across tables.

---

Why the structure matters

A table can be designed well or poorly. The difference determines whether queries are fast or slow, whether the data is consistent or contradictory, and whether the application is maintainable or fragile.

The schema problem. A table without a defined schema is a spreadsheet. Any value can go in any column. A date can be stored in a text field. A negative number can be stored in an age field. A table with a schema defines what each column represents and what values it can hold. The database rejects anything that does not fit .

The key problem. A table without a primary key can have duplicate rows. Two customers with the same name and address are indistinguishable. A primary key gives every row a unique identity. A foreign key connects that identity to rows in another table. Without keys, the relationships between tables are informal conventions that the database cannot enforce .

The constraint problem. A column can have constraints beyond its data type. It can be NOT NULL, meaning it must always have a value. It can be UNIQUE, meaning no two rows can have the same value. It can have a CHECK constraint, meaning the value must satisfy a condition. It can have a DEFAULT, meaning a value is supplied when none is given. Each constraint is a rule that the database enforces .

The normalization problem. A table that stores redundant data — the same customer address in every order — has update anomalies. Changing the address requires updating every order. A normalized design stores each fact once and uses foreign keys to connect the facts. The structure of the tables determines whether the data can be kept consistent.

The trade-off. A strict schema adds rigidity. Changing the structure of a table requires a migration, and migrations can be disruptive. A schema-less database allows any data to be stored at any time, which is convenient for prototyping but dangerous for production. The rigidity of the schema is the price of consistency. For applications where the data matters, the price is worth paying.

---

a. Tables, Rows, and Columns

A table is a collection of related data organized into rows and columns. It is the primary structure in a relational database. Every table has a name that is unique within its schema, and every table has a defined structure — the set of columns it contains and the constraints on those columns .

The table is the relation in the relational model. The rows are the tuples of the relation. The columns are the attributes. The number of columns is the degree of the table. The number of rows is the cardinality .

A row (also called a tuple or a record) is a single instance of the entity the table represents. In a customers table, each row is one customer. The row has a value for every column in the table. Some values may be NULL, which represents missing or inapplicable data. A NULL is not the same as an empty string or zero. It is the absence of a value .

A column (also called a field or an attribute) is a property of the entity. It has a name and a data type that defines the kind of values it can hold. The data type is the domain of the column. Common data types include:

Type	Description	Examples
INTEGER	Whole numbers	42, -7, 0
DECIMAL(p, s)	Exact decimal with precision and scale	999.99, 0.01
VARCHAR(n)	Variable-length text up to n characters	'Alice', 'hello'
TEXT	Unlimited-length text	Long descriptions
DATE	Calendar date	2026-09-30
TIMESTAMP	Date and time	2026-09-30 14:23:01
BOOLEAN	True or false	true, false
UUID	Universally unique identifier	'a1b2c3d4-...'

The data type constrains what can be stored. An INTEGER column rejects 'hello'. A DATE column rejects 'not a date'. The database validates every insert and update against the column’s type .

The column may also allow or disallow NULL values. By default, most columns allow NULL. A column with the NOT NULL constraint requires a value on every insert. The NOT NULL constraint is one of the most common and most valuable constraints because it prevents missing data in fields that are essential .

---

b. Primary Keys and Candidate Keys

A primary key is a column or set of columns that uniquely identifies each row in the table. No two rows can have the same primary key value, and the primary key cannot be NULL. Every table should have a primary key. Without one, the table cannot be reliably queried or joined .

A primary key can be natural or surrogate. A natural key is a value that has meaning outside the database — a Social Security number, an email address, an ISBN. A surrogate key is a value that is generated for the purpose of identification only — an auto-incrementing integer, a UUID. Surrogate keys are generally preferred because natural keys can change (a person changes their email) and because natural keys can be longer and slower to index .

A candidate key is any column or set of columns that could serve as the primary key. It is unique and not null, just like a primary key. A table can have multiple candidate keys. The database administrator selects one to be the primary key and marks the others with a UNIQUE constraint .

For example, in an employees table, both employee_id and email might be unique. employee_id is chosen as the primary key. email is declared with a UNIQUE constraint. Both prevent duplicates, but only one is the primary key.

A composite key is a primary key composed of more than one column. It is used when no single column is unique. In an order_items table, the combination of order_id and product_id is unique — the same product cannot appear twice in the same order. Neither column is unique on its own, but the combination is. The primary key is (order_id, product_id) .

---

c. Foreign Keys and Referential Integrity

A foreign key is a column or set of columns in one table that refers to the primary key (or a unique key) of another table. It creates a relationship between the two tables and enforces referential integrity — the guarantee that every foreign key value corresponds to an existing row in the referenced table .

CREATE TABLE orders (
    order_id     INTEGER PRIMARY KEY,
    customer_id  INTEGER NOT NULL,
    order_date   DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

The customer_id column in orders is a foreign key that references the customer_id column in customers. The database rejects any insert into orders with a customer_id that does not exist in customers. It also rejects any delete from customers that would leave an order without a customer, unless the foreign key is defined with ON DELETE CASCADE .

The referential actions determine what happens when the referenced row is deleted or updated. The standard actions are:

Action	Behavior
NO ACTION	Reject the operation (default)
RESTRICT	Reject the operation immediately
CASCADE	Propagate the operation to the referencing rows
SET NULL	Set the foreign key to NULL
SET DEFAULT	Set the foreign key to its default value

The NO ACTION and RESTRICT actions are similar. The difference is in when the check is performed. RESTRICT checks immediately. NO ACTION checks at the end of the transaction. For most applications, the difference does not matter. CASCADE is the most powerful — deleting a customer also deletes all their orders. It should be used carefully because a single delete can remove many rows.

A foreign key can also be self-referential. An employees table might have a manager_id column that references the employee_id column in the same table. This creates a hierarchy where each employee reports to another employee .

---

Complete Example Session

This session builds a small schema that demonstrates tables, rows, columns, primary keys, candidate keys, foreign keys, and referential actions.

-- ============================================
-- PART 1: THE CUSTOMERS TABLE
-- ============================================

CREATE TABLE customers (
    customer_id   INTEGER PRIMARY KEY,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL,
    email         VARCHAR(100) UNIQUE NOT NULL,
    phone         VARCHAR(20),
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- customer_id is the primary key (surrogate).
-- email is a candidate key with a UNIQUE constraint.
-- first_name and last_name are NOT NULL.
-- phone allows NULL.
-- created_at has a DEFAULT.

-- ============================================
-- PART 2: THE PRODUCTS TABLE
-- ============================================

CREATE TABLE products (
    product_id    INTEGER PRIMARY KEY,
    product_name  VARCHAR(100) NOT NULL,
    price         DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock         INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    category      VARCHAR(50)
);

-- product_id is the primary key.
-- price has a CHECK constraint.
-- stock has a DEFAULT and a CHECK constraint.

-- ============================================
-- PART 3: THE ORDERS TABLE WITH A FOREIGN KEY
-- ============================================

CREATE TABLE orders (
    order_id      INTEGER PRIMARY KEY,
    customer_id   INTEGER NOT NULL,
    order_date    DATE NOT NULL,
    status        VARCHAR(20) NOT NULL DEFAULT 'pending'
                  CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled')),
    total         DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- customer_id is a foreign key.
-- ON DELETE RESTRICT prevents deleting a customer with orders.
-- ON UPDATE CASCADE propagates customer_id changes.
-- status has a CHECK constraint with a list of allowed values.

-- ============================================
-- PART 4: THE ORDER_ITEMS TABLE WITH A COMPOSITE KEY
-- ============================================

CREATE TABLE order_items (
    order_id      INTEGER NOT NULL,
    product_id    INTEGER NOT NULL,
    quantity      INTEGER NOT NULL CHECK (quantity > 0),
    unit_price    DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON DELETE RESTRICT
);

-- The primary key is the combination of order_id and product_id.
-- The same product cannot appear twice in the same order.
-- Deleting an order deletes its items (CASCADE).
-- Deleting a product with items is prevented (RESTRICT).

-- ============================================
-- PART 5: THE EMPLOYEES TABLE WITH A SELF-REFERENTIAL FOREIGN KEY
-- ============================================

CREATE TABLE employees (
    employee_id   INTEGER PRIMARY KEY,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL,
    manager_id    INTEGER,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
        ON DELETE SET NULL
);

-- manager_id is a foreign key to the same table.
-- Deleting a manager sets the manager_id of their reports to NULL.

-- ============================================
-- PART 6: INSERTING DATA
-- ============================================

INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES (1, 'Alice', 'Johnson', 'alice@example.com');

INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES (2, 'Bob', 'Smith', 'bob@example.com');

INSERT INTO products (product_id, product_name, price, stock, category)
VALUES (101, 'Laptop', 999.99, 10, 'Electronics');

INSERT INTO products (product_id, product_name, price, stock, category)
VALUES (102, 'Mouse', 29.99, 50, 'Electronics');

INSERT INTO orders (order_id, customer_id, order_date, status)
VALUES (1001, 1, '2026-09-30', 'pending');

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1001, 101, 1, 999.99);

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1001, 102, 2, 29.99);

INSERT INTO employees (employee_id, first_name, last_name, manager_id)
VALUES (1, 'Carol', 'Williams', NULL);

INSERT INTO employees (employee_id, first_name, last_name, manager_id)
VALUES (2, 'David', 'Brown', 1);

-- ============================================
-- PART 7: TESTING THE CONSTRAINTS
-- ============================================

-- Duplicate email (UNIQUE violation)
INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES (3, 'Eve', 'Davis', 'alice@example.com');
-- Error: UNIQUE constraint failed: customers.email

-- Negative price (CHECK violation)
INSERT INTO products (product_id, product_name, price)
VALUES (103, 'Keyboard', -10.00);
-- Error: CHECK constraint failed: price >= 0

-- Orphan order (FOREIGN KEY violation)
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (1002, 999, '2026-09-30');
-- Error: FOREIGN KEY constraint failed

-- Delete a customer with orders (RESTRICT violation)
DELETE FROM customers WHERE customer_id = 1;
-- Error: FOREIGN KEY constraint failed

-- Delete an order with items (CASCADE)
DELETE FROM orders WHERE order_id = 1001;
-- The order_items for order 1001 are also deleted.

-- ============================================
-- PART 8: QUERYING THE SCHEMA
-- ============================================

-- List all tables in the schema
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public';

-- List columns for a table
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'customers';

-- ============================================
-- PART 9: THE RELATIONSHIP DIAGRAM
-- ============================================

-- customers (1) ───< orders (many)
--   customer_id          customer_id (FK)
--
-- orders (1) ───< order_items (many)
--   order_id              order_id (FK)
--
-- products (1) ───< order_items (many)
--   product_id            product_id (FK)
--
-- employees (1) ───< employees (many)
--   employee_id           manager_id (FK)

-- ============================================
-- PART 10: THE KEY CONCEPTS IN ONE VIEW
-- ============================================

-- Table: customers, products, orders, order_items, employees
-- Primary key: single column or composite
-- Candidate key: email (UNIQUE)
-- Foreign key: orders.customer_id → customers.customer_id
-- Foreign key: order_items.order_id → orders.order_id
-- Foreign key: order_items.product_id → products.product_id
-- Foreign key: employees.manager_id → employees.employee_id (self-referential)
-- Referential actions: RESTRICT, CASCADE, SET NULL
-- Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT

The ten parts cover the customers table, the products table, the orders table with a foreign key, the order_items table with a composite key, the employees table with a self-referential foreign key, inserting data, testing the constraints, querying the schema, the relationship diagram, and a summary of the key concepts.

---

Quick Reference

The Table Structure

Term	Definition
Table	Collection of rows and columns
Row	Single instance of an entity
Column	Property of the entity
Degree	Number of columns
Cardinality	Number of rows
Schema	Definition of columns, types, and constraints

The Key Types

Key	Definition
Primary key	Unique identifier for each row
Candidate key	Column(s) that could be the primary key
Composite key	Primary key with multiple columns
Foreign key	Column(s) referencing another table’s primary key
Surrogate key	Generated identifier (auto-increment, UUID)
Natural key	Identifier with external meaning

The Column Constraints

Constraint	Purpose
NOT NULL	Requires a value
UNIQUE	No duplicate values
CHECK	Value must satisfy a condition
DEFAULT	Value supplied when none given
PRIMARY KEY	Unique and not null
FOREIGN KEY	Reference to another table

The Referential Actions

Action	Behavior
NO ACTION	Reject (default)
RESTRICT	Reject immediately
CASCADE	Propagate to referencing rows
SET NULL	Set foreign key to NULL
SET DEFAULT	Set foreign key to default

---

Best Practices

✅ Do This:

-- Define a primary key for every table
CREATE TABLE users (user_id INTEGER PRIMARY KEY, ...);          -- ✅

-- Use NOT NULL for essential columns
email VARCHAR(100) NOT NULL                                    -- ✅

-- Use CHECK constraints for domain rules
price DECIMAL(10, 2) CHECK (price >= 0)                        -- ✅

-- Use foreign keys to enforce referential integrity
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)     -- ✅

-- Use composite keys for junction tables
PRIMARY KEY (order_id, product_id)                             -- ✅

❌ Don’t Do This:

-- Don't create a table without a primary key
CREATE TABLE logs (message TEXT);  -- no primary key             -- ❌

-- Don't use nullable columns for essential data
customer_id INTEGER  -- allows NULL                              -- ❌ use NOT NULL

-- Don't use CASCADE without understanding the consequences
ON DELETE CASCADE  -- deletes all referencing rows              -- ⚠️

-- Don't store multiple values in one column
tags VARCHAR(255)  -- 'red,green,blue'                          -- ❌ normalize

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Duplicate rows	No primary key	Add a primary key
Orphaned rows	No foreign key constraint	Add the foreign key
Invalid data	No CHECK constraint	Add the constraint
Missing values	No NOT NULL constraint	Add NOT NULL
Slow joins	No index on foreign key	Create an index
Cannot delete parent	Foreign key with RESTRICT	Use CASCADE or delete children first

---

Real-World Examples

1. Primary Key

CREATE TABLE users (user_id INTEGER PRIMARY KEY, ...);

2. Composite Primary Key

PRIMARY KEY (order_id, product_id)

3. Candidate Key

email VARCHAR(100) UNIQUE NOT NULL

4. Foreign Key

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

5. Cascade Delete

ON DELETE CASCADE

6. Restrict Delete

ON DELETE RESTRICT

7. Set Null

ON DELETE SET NULL

8. Check Constraint

age INTEGER CHECK (age >= 0 AND age <= 130)

9. Default Value

status VARCHAR(20) DEFAULT 'pending'

10. Self-Referential Foreign Key

FOREIGN KEY (manager_id) REFERENCES employees(employee_id)

---

Visual

The Table Structure

┌──────────────────────────────────────────────┐
│  TABLE: customers                            │
│                                              │
│  customer_id │ first_name │ last_name │ email│
│  ────────────┼────────────┼───────────┼──────│
│  1           │ Alice      │ Johnson   │ a@.. │
│  2           │ Bob        │ Smith     │ b@.. │
│  3           │ Carol      │ Williams  │ c@.. │
│                                              │
│  Columns define the structure.               │
│  Rows contain the data.                      │
│  customer_id is the primary key.             │
│                                              │
└──────────────────────────────────────────────┘

The Primary and Foreign Keys

┌──────────────────────────────────────────────┐
│  CUSTOMERS (1)                               │
│    customer_id (PK)                          │
│                                              │
│  ORDERS (many)                               │
│    order_id (PK)                             │
│    customer_id (FK → customers.customer_id)  │
│                                              │
│  One customer has many orders.               │
│  Each order belongs to one customer.         │
│                                              │
│  The foreign key enforces the relationship.  │
│                                              │
└──────────────────────────────────────────────┘

The Referential Actions

┌──────────────────────────────────────────────┐
│  REFERENTIAL ACTIONS                         │
│                                              │
│  Parent deleted?                             │
│    │                                         │
│    ├─ RESTRICT → reject the delete           │
│    │                                         │
│    ├─ CASCADE → delete the children too      │
│    │                                         │
│    ├─ SET NULL → children keep, FK becomes NULL│
│    │                                         │
│    └─ SET DEFAULT → FK becomes the default   │
│                                              │
│  Choose the action that matches the          │
│  business rule.                              │
│                                              │
└──────────────────────────────────────────────┘

The Composite Key

┌──────────────────────────────────────────────┐
│  ORDER_ITEMS                                 │
│                                              │
│  order_id │ product_id │ quantity │ unit_price│
│  ─────────┼────────────┼──────────┼──────────│
│  1001     │ 101        │ 1        │ 999.99   │
│  1001     │ 102        │ 2        │ 29.99    │
│  1002     │ 101        │ 1        │ 999.99   │
│                                              │
│  PRIMARY KEY (order_id, product_id)          │
│                                              │
│  Neither column is unique alone.             │
│  The combination is unique.                  │
│                                              │
└──────────────────────────────────────────────┘

---

Summary

Item	Value
Table	Collection of rows and columns
Row	Single instance of an entity
Column	Property with a name and data type
Primary key	Unique identifier for each row
Candidate key	Column(s) that could be the primary key
Composite key	Primary key with multiple columns
Foreign key	Reference to another table’s primary key
Referential integrity	Every foreign key references a valid row
Constraints	NOT NULL, UNIQUE, CHECK, DEFAULT
Referential actions	NO ACTION, RESTRICT, CASCADE, SET NULL, SET DEFAULT

Key takeaways:

• A table is a relation with rows and columns. The columns define the structure with names and data types. The rows contain the data. The schema is the definition of the table’s structure, and it is enforced by the database .
• A primary key uniquely identifies each row. It cannot be null and cannot be duplicated. Every table should have a primary key. A composite key is a primary key composed of multiple columns .
• A foreign key references the primary key of another table. It creates a relationship and enforces referential integrity. The database rejects any insert or update that would create a dangling reference .
• Referential actions determine what happens when a referenced row is deleted or updated. RESTRICT and NO ACTION reject the operation. CASCADE propagates it. SET NULL and SET DEFAULT modify the foreign key .
• Column constraints enforce data integrity. NOT NULL requires a value. UNIQUE prevents duplicates. CHECK validates a condition. DEFAULT supplies a value when none is given .
• The choice between natural and surrogate keys is a design decision. Natural keys have external meaning and can change. Surrogate keys are generated and stable. Surrogate keys are generally preferred for primary keys .
• The schema is the contract between the application and the database. Changing the schema requires a migration. The schema defines what the application can store and how the data is structured. A well-designed schema prevents data inconsistencies and makes queries efficient.

Remember: The table, the row, the column, the primary key, and the foreign key are the building blocks of every relational database. The table stores the data. The columns define the structure. The primary key gives each row an identity. The foreign key connects the tables. The constraints enforce the rules. The referential actions determine what happens when the rules are tested. Understanding these primitives is the foundation for writing SQL that works.

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!