Tables
Named relations: a heading of columns and a body of rows. Duplicates depend on whether you mean bag or set.
Level: beginner
What is it?
A table defines named columns and stores rows whose values correspond to those columns. A declared schema specifies data types and constraints. In SQL databases, tables normally behave as bags of rows, which means duplicates can exist unless a key, unique constraint, or query operation prevents them.
Why does it matter?
Well-designed tables make valid data easier to store and invalid states harder to represent. Column types and constraints catch errors close to the data rather than relying entirely on every application client.
Mental model
Treat a table as a contract: every row has the same set of named attributes, and each column has a declared meaning and type. The table's physical row order is not a reliable API.
How it works
- Use types that match the domain, such as integer identifiers, timestamps for instants, and decimal types for exact currency values.
- Use NOT NULL, CHECK, UNIQUE, primary keys, and foreign keys to express invariants.
- Do not assume insertion order defines retrieval order; specify ORDER BY.
- Normalization can split one wide table into related tables to reduce update anomalies.
- Indexes are separate access structures; adding an index does not change the table's logical meaning.
Steps
- Create an orders table with order_id as primary key and customer_id as a foreign key.
- Insert a row and let the DBMS enforce types and constraints.
- Query a subset with WHERE and choose columns with SELECT.
- Use ORDER BY created_at, order_id if stable ordering is needed.
Real-world usage
- Modeling records, events, products, accounts, and other entities.
- Explaining schema constraints and the difference between logical and physical ordering.
- Reviewing schema design before API development.
Common mistakes
- Relying on row position as identity.
- Using text for every field and validating only in frontend code.
- Allowing duplicates in a field meant to uniquely identify a record.
Interview questions
Does a SQL table guarantee row order?
No. Query results are unordered unless ORDER BY specifies an order.
Why should important invariants be declared as database constraints?
The database can enforce them for every writer, including scripts, jobs, and other applications, instead of relying on each caller to behave correctly.