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.
#Intermediate
Material for readers past the basics.
145 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...
HTML
details, summary, time, address and other Semantic Elements
A disclosure widget with no JavaScript, machine readable dates, contact details and a progress bar. Small elements that replace a surprising amount of...
HTML
section, article and aside
The three most argued about elements in HTML. There are clear tests for each, and once you know them the choice stops being a matter of taste.
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.
HTML
textarea, File Uploads and Hidden Fields
Three controls with their own rules: a textarea whose value is its content, a file input that needs the right enctype, and hidden fields that are not...
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...
HTML
select, option, optgroup and datalist
Dropdowns done properly: grouping options, multiple selection, the placeholder option trick, and when a datalist beats a select.
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.