Relational / SQL
The relational tier, in two halves: `db-relational-concepts` holds everything true of every SQL engine — the model, schema and constraints, transactions and isolation, indexes, the planner, storage and recovery, replication, partitioning and access control — and the eleven engine subtrees beside it hold what is true of one product only. A generic roadmap references the concepts child; a roadmap that names an engine references that engine.
on this pageshowhide
explore
- Relational Database Concepts (has its own guide)683 questions
- Relational Model & Theory49 questions
- Schema Design & Normalization94 questions
- Keys & Integrity Constraints48 questions
- Transactions & Concurrency Control108 questions
- Indexes & Access Paths67 questions
- Query Processing & Optimization72 questions
- Views, Procedures & Server-Side Logic46 questions
- Engine Architecture & Storage Internals54 questions
- Replication, Backup & High Availability54 questions
- Partitioning & Scaling46 questions
- Access Control & Data Protection45 questions
- PostgreSQLempty
- SQL and Data Typesempty
- JSONB and Extensionsempty
- Replication and WALempty
- Performance Tuningempty
- MySQLempty
- Schema Designempty
- Storage Enginesempty
- Indexingempty
- Replicationempty
- Performance Tuningempty
- MariaDBempty
- SQLiteempty
- Oracle Databaseempty
- MS SQL / SQL Serverempty
- T-SQL Fundamentalsempty
- Schema & Indexingempty
- Performance Tuningempty
- SAP HANAempty
- Amazon RDSempty
- Amazon Auroraempty
- Azure SQL Databaseempty
- Google Cloud SQLempty
→ has its own guide
- AI & Data Scientistrole
- AI Engineerrole
- Android Developerrole
- BI Analystrole
- Backend Developerrole
- BigQueryskill
- Computer Scienceskill
- Cyber Security Expertrole
- Data Analystrole
- Data Engineerrole
- DevOps / SRE Engineerrole
- DevSecOps Engineerrole
- Forward Deployed Engineerrole
- Full Stack Developerrole
- Java Backend Developerrole
- Java SDETrole
- Kotlin Backend Developerrole
- MLOps Engineerrole
- Machine Learning Engineerrole
- MongoDBskill
- PostgreSQL DBArole
- QA Engineerrole
- SQLskill
- Server-Side Game Developerrole
- Snowflakeskill
- Software Architectrole
questions
683 · 1 sectionWhat does a CHECK constraint do in a relational table, and at what point does the database engine evaluate it?
basics
~20 sA CHECK constraint is a boolean condition attached to a table. The engine tests it for every row a statement inserts or updates; if the condition comes out false, the statement fails with a constraint-violation error and the row is not stored.
If the application already validates every field before saving, why still declare constraints such as NOT NULL, unique keys and foreign keys in the database schema?
basics
~20 sBecause the application is not the only writer, and its checks are not atomic with the write. Scripts, migrations, other services and future bugs all reach the same tables. Database constraints are the last line of defense that no code path can bypass; app validation exists for fast, friendly feedback.
A column in one table references the key of another table under a foreign key constraint. What exactly does that constraint guarantee about the stored data, and what happens if the referencing column holds NULL?
basics
~20 sIt guarantees every non-NULL value in the referencing (child) column exists in the referenced (parent) key, so there are no orphan rows. A NULL referencing value points at nothing, so the check is skipped and the row is accepted.
A table column is declared NOT NULL with a DEFAULT value. Explain the difference between an INSERT that omits that column entirely and one that explicitly supplies NULL for it.
basics
~20 sOmitting the column makes the engine apply the DEFAULT, so the insert succeeds. Explicitly writing NULL is a real value being supplied — the DEFAULT is not consulted, and the NOT NULL constraint rejects the row.
What does declaring a primary key on a table actually guarantee, and how does a relational engine enforce that guarantee?
basics
~20 sA primary key guarantees every row has a value in those columns (no NULLs) and no two rows share the same combination. The engine enforces it by marking the columns NOT NULL and maintaining a unique index it probes on every insert and key update.