Page 16 - SQL

A foreign key is not an optional decoration, it is the mechanism a database uses to enforce referential integrity itself instead of relying on application code. This post explains FOREIGN KEY syntax, the behavior of ON DELETE CASCADE, RESTRICT and SET NULL in detail, what ON UPDATE means, correct indexing of foreign key columns, and why disabling FK checks for supposed convenience almost always leads to data corruption.

A trigger can guarantee data integrity and audit trails regardless of which application is writing. It can also create invisible, hard to maintain business logic if used the wrong way. This article covers BEFORE versus AFTER, row level versus statement level, real audit trail patterns, recursion traps, and clear criteria for when a trigger should be used sparingly and deliberately.

A regular view is nothing more than a stored query that gets re-executed on every access and therefore always returns current data, but occupies no storage for the results. A materialized view stores the result physically and, on access, only reads that stored data, making it faster but potentially stale until a refresh synchronizes the data again.

Insert or update in a single statement sounds like a simple problem, yet every database solves it with its own syntax, its own atomicity guarantees, and its own pitfalls. Anyone who wants to keep upsert code portable across MySQL, PostgreSQL, SQL Server, Oracle, and SQLite needs to understand these differences before the first production switch happens.

A clone of the production database is tempting for testing: realistic data volumes, real edge cases, no tedious test data creation. That is exactly what turns it into a compliance risk, the moment real customer data ends up in an environment with weaker access control and a broader pool of users. This article shows how to systematically anonymize staging databases without losing test expressiveness, and how referential consistency across foreign keys is preserved along the way.

Treating HAVING and WHERE as interchangeable filter clauses produces queries that either work by accident or run needlessly slow. The difference lies in execution order: WHERE filters before aggregation, HAVING after, and that is exactly what decides what can be filtered at all.

The ACID properties are the promise every relational database makes that a transaction takes effect either completely or not at all, that integrity rules always hold, that concurrent transactions cannot corrupt each other, and that confirmed data survives a crash. Understanding what actually breaks without each of these four properties leads to better decisions when designing transactions.

Data quality is not a state reached once and then held forever, it is an ongoing process tested anew with every import, every migration, and every deployment. Anyone who only runs quality checks occasionally and manually usually discovers problems only once they have already distorted reports or damaged downstream systems. This article shows which SQL based checks can be automated into pipelines, how threshold based alerting works, and how a concrete check can actively prevent a faulty deployment.

When a reporting query needs to combine data from two separate databases, many teams reach for an ETL pipeline by default: export data from system A, import it into system B, then join. Foreign data wrappers offer a more direct route. They expose an external data source, whether another relational database, a CSV file, or a REST API, as a virtual table inside your own database. From the query optimizer's perspective, that table looks like any other, with the same filtering, joining and aggregation options, but with different performance characteristics you need to understand.

A recursive CTE solves exactly the task where a normal query fails: traversing tree structures of unknown depth in a single SQL statement. With WITH RECURSIVE, category trees, org charts, and bills of materials are evaluated without application code and without a fixed nesting depth, as long as the anchor query, the recursive part, and the termination condition are defined cleanly.