SQL
Natural vs Surrogate Keys
Should the primary key come from the data or be generated? The trade offs, the failure modes of each, and the pattern most teams settle on.
#SQL
Notes related to SQL.
131 notes tagged
SQL
Candidate, Primary, Alternate and Composite Keys
The vocabulary of keys, explained on one table: which columns could identify a row, which one you choose, and what happens to the rest.
SQL
Referential Integrity, Cascades and Constraint Management
What happens to child rows when a parent is deleted or updated, how to choose between RESTRICT, CASCADE and SET NULL, and how to add, drop and disable...
SQL
PRIMARY KEY and FOREIGN KEY
The two constraints that give a database its structure: one that identifies a row, and one that guarantees a reference points at something real.
SQL
NOT NULL, DEFAULT, UNIQUE and CHECK
The four constraints that guard a single table. What each one enforces, how NULL interacts with UNIQUE and CHECK, and why the database is the right pl...
SQL
Recursive CTEs
Query a hierarchy of unknown depth, generate a series of rows, or follow a chain of references - all with one self referencing CTE.
SQL
WITH: Single and Multiple CTEs
A CTE names a query so the rest of the statement can read it like a table. Learn single and chained CTEs, and when a CTE beats a subquery or a view.
SQL
INTERSECT, EXCEPT and Column Compatibility
The two set operations MySQL waited longest to add: rows in both sets, and rows in one set but not the other - plus how to emulate them where they are...
SQL
UNION and UNION ALL
Stacking two result sets on top of each other. Learn the column compatibility rules, why UNION ALL is faster, and where ORDER BY belongs.
SQL
Subqueries in SELECT, FROM and WHERE: Join vs Subquery vs EXISTS
The same question answered four ways, with a straight comparison of readability, NULL safety and cost - and a rule for choosing between them.
SQL
Correlated Subqueries, EXISTS and NOT EXISTS
Subqueries that reference the outer row and run once per row. Learn EXISTS and NOT EXISTS, why they are NULL safe, and when correlation costs too much...
SQL
Multi Row Subqueries: IN, ANY and ALL
Subqueries that return a list of values, and the three operators that consume them - including the NULL trap that makes NOT IN return nothing.