Relational Database Concepts

Tables, rows and columns

A table describes one kind of thing, such as customers or orders. Each row is one instance and each column is one attribute with a fixed data type. The set of tables, columns, types and rules is the schema.

Keys

• A primary key is a column (or combination of columns) that identifies each row uniquely. It cannot be empty or repeated. Often it is a generated number or a UUID.
• A foreign key is a column that holds the primary key of a row in another table. The database refuses values that point to a row that does not exist (referential integrity).
• A unique constraint forbids duplicates in a column that is not the primary key, for example an email address.

Relationships

Kind	Example	How it is stored
One to many	One customer has many orders	Foreign key on the “many” side (orders.customer_id)
Many to many	Students and courses	A separate join table holding two foreign keys
One to one	User and profile	Foreign key that is also unique

Other constraints

NOT NULL requires a value, CHECK validates a rule (for example price >= 0) and DEFAULT supplies a value when none is given. Constraints are a safety net: they protect the data even when application code has a bug.

Normalization

Normalization means organising tables so each fact is stored once. If a customer’s address is copied into every order row, changing it means updating many rows, and missing one makes the data contradict itself. The usual fix is to move the address into a customers table and reference it with a foreign key. The common “normal forms” (1NF, 2NF, 3NF) are steps towards this. Sometimes data is deliberately denormalized for read speed, as a conscious trade-off. See Data Modeling.

NULL, products and cross-table queries

• NULL means “unknown or missing”, not zero or an empty string, and it compares differently: = NULL never matches, use IS NULL.
• Products differ (PostgreSQL, SQL Server, MySQL, SQLite), but the concepts above are shared.
• Querying across tables is covered in SQL Fundamentals.

Try it

Create a small SQLite or PostgreSQL database with customers and orders tables, where orders.customer_id is a foreign key. Insert an order for a customer that does not exist and read the error. Then try deleting a customer who has orders.