SQL interview questions with model answers
51 questions written alongside the free SQL course, each with the answer an interviewer is listening for. 30 are below, spread from first principles to advanced; the rest are in the course.
What SQL interviews test
SQL interviews test whether you can turn a question into a correct query: joins and their NULL behaviour, GROUP BY with HAVING, subqueries versus CTEs, window functions such as ROW_NUMBER and running totals, and how an index changes what a query costs.
1. When would you use a temporary table instead of a CTE?
When the intermediate result is read more than once, when it needs an index, or when it is large enough that I want it materialised rather than possibly re-evaluated. A CTE is a name for a query; depending on the engine and the plan it may be inlined and computed more than once. A temp table is a guarantee that the work happened exactly once, and it gives me somewhere to put an index. The costs are a write, no statistics until I gather them, and — on a pooled connection — a cleanup obligation. If the result is read once and small, I use a CTE, because it reads better and avoids the write.
From the lesson Temporary Tables
2. What is the difference between a local and a global temporary table, and when would you use a global one?
In SQL Server, #t is local to my session — the engine gives it a hidden suffix so concurrent sessions cannot collide — and ##t is global to the instance with a literal name, visible to every session, dropped only when the creator disconnects and nobody else is referencing it. I would be reluctant to use a global one: it is shared mutable state with no access control and a lifetime nobody owns, so two runs of the same job race each other. If I genuinely need data to cross sessions I use a permanent table keyed by a run identifier, which is portable, visible to monitoring, and cannot collide. I would also separate out Oracle, where GLOBAL TEMPORARY TABLE means a shared definition with per-session rows — closer to SQL Server's #t than its ##t, despite the name.
From the lesson Local and Global Temporary Tables
3. How do you return the highest-paid employee in each department?
A window function inside a derived table, filtered outside it: ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) in a subquery in FROM, then WHERE rn = 1 in the outer query. It has to be wrapped because window functions are computed after WHERE, so you cannot filter on one directly. The choice of ranking function matters and is the follow-up they are usually waiting for: ROW_NUMBER gives exactly one row per department even on a tie, RANK gives all tied top earners, and DENSE_RANK matters once you want the top N rather than the top one. If ties should all be returned, RANK is the correct choice and ROW_NUMBER silently drops people.
From the lesson Derived Tables
4. What does LATERAL do, and when would you reach for it?
It lets a subquery in FROM reference columns from the items to its left, so it is evaluated once per outer row instead of once overall. I reach for it in two situations. First, top-N-per-group where N is more than one and I want several columns back — a correlated scalar subquery only gives one value, and one per column means repeating the same scan. Second, expanding each row into a variable number of rows, like unnesting an array or a JSON document per row. The performance argument is that LATERAL with LIMIT can stop early per outer row, where the window-function rewrite ranks everything and throws most of it away — but that depends on having the right index, and it is a nested loop, so on a big outer table it can go the other way. In SQL Server the same thing is spelled CROSS APPLY, and I would use LEFT JOIN LATERAL … ON true rather than the cross form whenever groups with no matches must survive.
From the lesson PostgreSQL LATERAL
5. What is CROSS APPLY and how does it differ from a JOIN?
CROSS APPLY evaluates its right-hand side once per left-hand row and lets that expression reference the left-hand row's columns. A JOIN cannot do that: its two sides are independent. Two consequences. First, it is how you write top-N-per-group where N is more than one, or call a table-valued function with a column as an argument — that second case is impossible any other way. Second, it is a nested loop, so it wins when there is an index on the correlation and sort columns and the outer set is small, and loses badly without one. It behaves like an INNER JOIN, so outer rows whose subquery returns nothing are dropped; OUTER APPLY keeps them. It is SQL Server and Oracle syntax — PostgreSQL and MySQL 8.0.14+ call the same thing CROSS JOIN LATERAL.
From the lesson SQL Server CROSS APPLY
6. What does the WINDOW clause do, and does it make the query faster?
It names an OVER specification so several window functions can share one definition instead of repeating it. It sits after HAVING and before ORDER BY. It does not make the query faster — engines already recognise that functions sharing a specification can share one sort and partition pass, so the plan is normally identical. The benefit is that the intent is stated once and there is one place to change it; repeating a PARTITION BY four times and updating three of them is a real bug that still returns plausible numbers. The one performance point worth making is indirect: two differently-ordered named windows means two sorts, and naming them makes that obvious in a way four inline clauses do not. The practical constraint is SQL Server, which only got the WINDOW clause in 2022.
From the lesson Named Windows
7. When is dynamic SQL the right tool, and what are its risks?
It is right when an identifier is not known until runtime — a table or column name, which cannot be a parameter in any engine — or when the query shape genuinely varies enough that the static catch-all version produces a bad plan. A search screen with twelve optional filters is the usual honest example. The risks are three. Injection, if any value is concatenated rather than bound; the fix is that values are always parameters and identifiers are validated against an allow-list and escaped with the engine's own quoting function. Plan-cache pollution, if the statement text varies per call, which burns compilation CPU and evicts plans that mattered. And maintainability — the SQL is invisible to static analysis and dependency tracking, and errors move from compile time to run time. So I would use it deliberately and narrowly, and I would not reach for it to avoid writing two static statements.
From the lesson Dynamic SQL
8. How do you write dynamic SQL safely?
By splitting the input into two categories and handling each one way. Values are always bound as parameters — sent separately from the statement text, never parsed as SQL, so there is no string to close and nothing to escape. Identifiers cannot be parameters in any engine, so they are validated against an allow-list I control and then wrapped with the engine's own quoting function, QUOTENAME or quote_ident or DBMS_ASSERT. Two independent controls, so one mistake is not enough. What I would specifically not do is escape values by hand or strip dangerous keywords — a deny-list enumerates what the author imagined, and encodings get past it. I would also mention that in SQL Server the mechanism matters: EXEC(@sql) cannot take parameters at all, so sp_executesql is the only acceptable form. And dynamic SQL inside a procedure runs with the procedure's privileges, which makes injection there an escalation, not just a disclosure.
From the lesson Safe Dynamic SQL with Parameters
9. How would you turn a wide table with one column per month into one row per month?
In SQL Server or Oracle, UNPIVOT. Everywhere else, either a UNION ALL with one SELECT per column, or — better on a large table — a cross join against a small list of the column names with a CASE picking the matching value, which reads the source once instead of once per column. The behaviour I would call out is NULLs: UNPIVOT drops them by default, so a department that reported three of four quarters produces three rows, and if the question is 'how many periods reported' that silently gives the wrong answer. Oracle has INCLUDE NULLS; SQL Server does not, and the portable rewrites keep NULLs unless you filter them, which makes it an explicit choice. More generally I would push back on the schema: if data is being unpivoted on every read, it is stored in the wrong shape. Long is easier to aggregate and filter, and adding a period is a row rather than a schema change.
From the lesson UNPIVOT
10. Should business logic live in stored procedures?
It depends on which logic and what the constraints are, and I would not answer it as a matter of principle. The real arguments for are data movement — aggregating a million rows and returning twelve keeps the million on the server — round trips, atomicity, and using EXECUTE as a permission boundary so a caller gets the operation without rights on the tables. The real arguments against are that procedures live outside version control unless you work at it, so they escape review and rollback; that they are hard to test without a database; that they are the least portable thing in SQL, four incompatible languages; and that they consume database capacity, which is the hardest capacity to scale. Where I land in practice: validation and integrity rules belong in the database as constraints, set-oriented data manipulation is fine in a procedure if it is genuinely set-oriented, and application and workflow logic belongs in the application. What I would push back on hardest is a procedure that loops row by row — that gives up the only advantage SQL has and is usually slower than the statement it replaced.
From the lesson Stored Procedure Fundamentals
11. What is the difference between an output parameter and a returned result set?
An output parameter carries one scalar; a result set carries rows. 'How many did you archive' is an output parameter, 'which ones did you archive' is a result set. The mistake I would flag is packing rows into a delimited string to return through an output parameter — the caller then has to parse it, and eventually meets a value containing the delimiter. Two engine-specific traps worth knowing. In SQL Server, RETURN is not a return value: it sends a single integer intended as a status code, 0 meaning success by convention, so using it for a count collides with that convention and with the integer range. And in PostgreSQL, procedures return through INOUT parameters rather than OUT — a plain OUT is not usable from CALL, which surprises people coming from other engines. More generally, if all I need is 'what did that statement do', RETURNING or the OUTPUT clause gets it from the statement itself without a procedure at all.
From the lesson Input, Output and Input-output Parameters
12. What does it mean to mark a function IMMUTABLE, and what happens if you get it wrong?
It promises the database that the same arguments always produce the same result, forever. That promise is what lets the planner evaluate the function once instead of per row, and — the important one — lets you build an index on its output. Getting it wrong is a correctness bug rather than a performance one: the index stores what the function returned at write time, and if the function's answer later differs, the index disagrees with the table. The symptom is rows silently missing from query results, with no error anywhere, which makes it one of the nastier things to debug. The rule I use is that anything reading a table is at best STABLE, anything touching the clock or a random source is VOLATILE, and only pure computation over the arguments is IMMUTABLE. Engines differ in how much they trust you: Oracle and PostgreSQL take your word, SQL Server infers determinism itself, and MySQL will not even create an unmarked function when binary logging is on because statement-based replication would produce different data on the replica.
From the lesson User-defined Functions
13. What is the difference between an inline and a multi-statement table-valued function?
An inline TVF is a single RETURN (SELECT …) and SQL Server expands it into the calling query like a view — the optimiser sees through it, pushes predicates in, uses the underlying indexes and has real statistics. A multi-statement TVF declares a table variable, fills it over several statements and returns it, and the optimiser cannot see inside: no predicate pushdown, no statistics, and an estimated row count that is 1 under the legacy cardinality estimator, 100 from 2014, and the actual value from 2017 with interleaved execution. So on anything older than 2017 a multi-statement TVF returning fifty thousand rows gets a plan built for one, and the query degrades non-linearly. The trap is that the two are distinguished by shape rather than a keyword — adding one statement to an inline function silently converts it. And I would ask whether a function is needed at all: if it takes no parameters it should be a view, and if the parameter is only a filter, a view plus a WHERE clause gives the optimiser more to work with.
From the lesson Table-valued Functions
14. When is a loop appropriate in a stored procedure?
Rarely, and almost never over rows. A loop that reads a row, computes something and updates it turns one statement into N, each with its own parse, plan, lock and log record — usually one to three orders of magnitude slower than the single statement it replaced, and it is the most common procedural performance mistake I see. Nearly all of them are an UPDATE with a CASE expression, or an UPDATE from a join. There are two legitimate cases. Batching: updating ten million rows in one statement holds locks for the duration and grows the transaction log until something fails, so a loop that does fifty thousand at a time and commits between batches is the standard safe pattern — and note the loop is over batches, not rows. And genuinely iterative algorithms where each step depends on the last and it cannot be written as a recursive CTE. Outside those two I would treat a row loop as a rewrite waiting to happen.
From the lesson Variables and Control Flow
15. What happens to the transaction when an error is caught in a stored procedure?
It depends on the engine, and assuming otherwise is how half-transactions get committed. In PostgreSQL a PL/pgSQL block with an EXCEPTION clause sets an implicit savepoint, so reaching the handler rolls back everything that block did — that is the behaviour most people assume they have everywhere, and they do not. In SQL Server, MySQL and Oracle, catching an error does nothing to the transaction: it is still open, the statements before the failure are still pending, and if the handler returns without deciding, the caller inherits it. SQL Server adds a further wrinkle — some errors leave the transaction doomed, meaning open but only capable of being rolled back, so a CATCH block that logs before rolling back will have its logging statement fail and will report the wrong error. My rule is: check the transaction state, roll back explicitly, log server-side, and then re-raise with the mechanism that preserves the original error — THROW, RESIGNAL or RAISE, not a freshly constructed one. And never WHEN OTHERS THEN NULL; a swallowed error becomes an incident weeks later with no trace of its cause.
From the lesson Exception and Error Handling
16. What is a trigger, and when would you use one?
Code the database runs automatically when a table is written to, defined by timing — BEFORE, AFTER or INSTEAD OF — event, and level. I use them for two things: audit trails, because a trigger fires on the write itself rather than on a code path someone has to remember to call, so it cannot be bypassed; and enforcing an invariant that must hold against every writer, including the manual fix applied at 2am. What I avoid is business logic, and the reason is invisibility rather than performance: someone reads the application code, sees one INSERT, and reasons about what it does — the trigger is not in that file, not in the stack trace, and often not in the repository. A debugging session that ends at a trigger is a long one. The engine-specific trap worth knowing is that SQL Server has no row-level triggers at all: the trigger fires once per statement with inserted and deleted holding every affected row, so a trigger written as though it fires per row silently processes one row of a ten-row insert. And firing order between triggers on the same event is unspecified in most engines — PostgreSQL defines it as name order, nobody else does — so two triggers where one depends on the other is a bug waiting for an upgrade.
From the lesson Trigger Fundamentals
17. How would you build an audit trail for a table?
An AFTER trigger on insert, update and delete, writing to an append-only audit table. AFTER rather than BEFORE because I want a record of what actually happened — a row rejected by a constraint or a BEFORE trigger never reaches an AFTER trigger, which is the correct behaviour for an audit. Each audit row records the operation, the actor, the timestamp, and both the old and new values, because the question an audit log gets asked is almost always 'what was it before and who changed it'. Two details that matter. The audit table must be append-only to the people it audits — the trigger runs with rights they do not have, via SECURITY DEFINER in PostgreSQL or the table owner's context elsewhere, so they can append but not rewrite history. And the change test has to be null-safe: OLD.col <> NEW.col is UNKNOWN when either side is NULL, so a value going from NULL to something silently fails to log. In PostgreSQL I would write one generic trigger function using to_jsonb(NEW) and TG_TABLE_NAME and attach it to every table, rather than one function per table. In SQL Server the body has to be set-based over inserted and deleted, because the trigger fires once per statement rather than per row.
From the lesson AFTER Triggers
18. How do you make a view over a join writable?
With INSTEAD OF triggers — one for insert, one for update, one for delete. The reason a join view is not writable by default is that there is no unambiguous mapping from a view row back to rows in several base tables: an update could mean several different things and the engine declines to guess. An INSTEAD OF trigger is you supplying that answer; the statement the caller issued never runs, and the trigger body decides which tables to touch and in what order. Where I would actually use it is as a stable interface — callers write to a view while the tables underneath are being migrated, or to hide a normalisation they should not have to know about — combined with granting write access to the view and none to the base tables, which makes it a real permission boundary. What I would not do is use it to save a caller from writing two statements, because it means the code in the application no longer describes what happens to the database, and every future debugging session has to find the view, then the trigger, then read the body. Two engine notes: PostgreSQL makes simple views automatically updatable so you often need no trigger at all, and SQL Server's INSTEAD OF triggers are statement-level, so the body has to be set-based over inserted and deleted.
From the lesson INSTEAD OF Triggers
19. What happens if a trigger updates the table it is attached to?
It depends entirely on the engine, which is the first thing I would say. SQLite has recursion off by default and a pragma to enable it. MySQL forbids a trigger from modifying its own table at all — error 1442 — so the situation cannot arise. PostgreSQL and Oracle allow it with no setting to turn it off, so the guard is entirely the trigger author's responsibility. SQL Server is the subtle one: RECURSIVE_TRIGGERS is off by default, but that only blocks direct recursion — a trigger on A writing to B whose trigger writes back to A is always allowed and is limited only by the 32-level nesting cap, and the accidental cycle is nearly always that indirect one. The guards are pg_trigger_depth() in PostgreSQL and TRIGGER_NESTLEVEL() in SQL Server, but the better guard is usually to only write when the value would actually change, so the recursive write never happens. And the best answer is to remove the need: the most common cause of accidental recursion is a trigger maintaining a derived column in its own table, and that should be a generated or computed column, which cannot recurse because it is not a write.
From the lesson Trigger Recursion
20. Design an audit trail for a table holding customer credit limits.
AFTER triggers on insert, update and delete, writing to an append-only audit table. AFTER because I want what actually happened — a row rejected by a constraint never reaches an AFTER trigger. Triggers rather than application logging because the trigger fires on the write, so it catches the migration script, the manual fix and the second service, none of which will remember to call a logging function. The audit row records operation, UTC timestamp, both the database user and the application user, the row key, and the old and new values — the old values especially, because 'what was it before' is what an audit gets asked and it is the column most often missing. Two things I would be explicit about. Under connection pooling current_user is the pooled account and is the same for every request, so the application user has to be passed in through a session variable — SET LOCAL app.user_id in PostgreSQL, SESSION_CONTEXT in SQL Server — and the trigger records both. And the trail has to be append-only to the people it audits: the trigger runs with rights they do not have, they hold no UPDATE or DELETE on the audit table, and I would add triggers on the audit table refusing updates and deletes as a second control. If the table were very hot I would compare this against change data capture, which does not extend the writer's transaction — but CDC and temporal tables do not capture who, and who is usually the reason the audit exists.
From the lesson Audit Triggers
21. What is GO in T-SQL?
A batch separator, and specifically not SQL — the server has never heard of it. It is an instruction to the client tool meaning 'send everything since the last GO as one batch', so a driver or ORM that does not implement it will send the word to the server and get a syntax error. Three consequences matter in practice: a variable declared in one batch does not exist in the next, CREATE PROCEDURE must be the first statement in its batch which is why GO precedes every one, and GO 5 runs the preceding batch five times. While on T-SQL specifics I would mention two others that catch people. SET NOCOUNT ON should be the first line of every procedure, because otherwise each statement sends a row-count message and some drivers surface those as empty result sets. And RETURN in a procedure is an integer status code, not a return value — data leaves through an OUTPUT parameter or a result set, and overloading RETURN with a count collides with the 0-means-success convention.
From the lesson SQL Server T-SQL
22. What is MySQL's DELIMITER directive for, and what does error 1418 mean?
DELIMITER changes the statement terminator while a stored program is being defined. The client normally ends a statement at a semicolon, and a procedure body is full of them, so without it the client sends only the fragment up to the first internal semicolon and MySQL reports a syntax error on a line that looks fine. It is a client directive rather than SQL, which is why it works in the command-line client and not in tools that send statements one at a time. Error 1418 is different and more interesting: with binary logging enabled, MySQL refuses to create a function that declares none of DETERMINISTIC, NO SQL or READS SQL DATA. The reason is statement-based replication — the statement is replayed on the replica, and a non-deterministic function would produce different data there, so the replica silently diverges. The common response is to set log_bin_trust_function_creators = 1, which silences the error without fixing the divergence. The real fixes are to declare the function honestly, switch to row-based binary logging so the replica receives resulting rows rather than the statement, or generate the value in the application.
From the lesson MySQL Stored Programs
23. Should business logic live in the database?
I would split the question, because 'business logic' covers two different things. Integrity rules — types, keys, ranges, references, 'an order total may not be negative' — belong in the database as constraints, and the argument is not performance, it is that a constraint cannot be forgotten. Application validation protects the paths that call it, and every real system accumulates other paths: the backfill, the 2am data fix, the integration nobody documented. A CHECK constraint is subject to all of them. Business rules that change with the business — pricing tiers, eligibility, workflow — belong in the application, where they can be versioned, unit-tested and deployed and rolled back with the release. In between there is a narrow band of set-oriented data manipulation, a nightly aggregation or a bulk archive, where doing it near the data is obviously right because it reduces what crosses the network. On the costs, I would be honest that the version-control objection is the strongest one and that it is fixable: CREATE OR REPLACE scripts in the repository, applied by a migration tool, with a drift check in CI. What I would not do is argue it as doctrine. 'Never put logic in the database' rules out constraints and audit triggers, which are the two things the database is unambiguously best at.
From the lesson When Logic Should Not Live in the Database
24. What is the difference between json and jsonb in PostgreSQL?
json stores the original text verbatim and reparses it on every read. jsonb parses once at write time into a binary structure, so reads traverse it directly. That makes jsonb slightly dearer to write and much cheaper to read, and — the decisive part — jsonb is the only one that can be GIN-indexed and the only one with the containment and key-existence operators, @> and ?. So jsonb is the default and json is the specialised type. The one case where json is correct is when the exact bytes matter, because jsonb normalises: it reorders keys, drops duplicates and reformats whitespace, so a document does not round-trip. If you have computed an HMAC over a signed webhook payload, jsonb will break the verification. The usual pattern there is to keep the raw text in a json or text column for the signature and derive a jsonb generated column from it for querying. One more practical point: a GIN index supports containment and key existence, not arbitrary path extraction, so on an indexed jsonb column you want WHERE payload @> '{"type":"x"}' rather than WHERE payload ->> 'type' = 'x' — the second one scans.
From the lesson PostgreSQL JSONB
25. How would you query an array inside a JSON column?
Expand it into rows and then treat it like any other relation. The function differs by engine — json_each in SQLite, jsonb_array_elements in PostgreSQL, JSON_TABLE in MySQL and Oracle, OPENJSON in SQL Server — but the shape is the same: it is a table-valued function correlated to each row, which is the LATERAL idea from earlier. Once the array is rows you can filter it, group it and join it to real tables, which is usually the point: expanding an items array and joining each sku to a products table. Two details that catch people. Use ->> rather than -> for the join key and for anything you compare, because -> keeps the JSON quotes and the comparison silently matches nothing. And cast before aggregating, because ->> returns text — summing it works by coercion in SQLite and misbehaves elsewhere. For a document whose shape I do not know, json_tree or jsonb_path_query walks the whole thing recursively, and grouping that by path across a whole table is the fastest way I know to discover what a collection of undocumented payloads actually contains. The design point underneath all of it: if I am expanding the same array on every query, it probably wanted to be a child table.
From the lesson Querying Nested JSON
26. How would you make a query on a JSON field fast?
First I would ask whether it should be a JSON field at all — if it is filtered on in every query it is not semi-structured data, and a real column gives typing, constraints, permissions and a simpler index. Assuming it stays in the document, the portable answer is a generated column extracting that path, stored, with an ordinary B-tree index on it. The value is computed once at write time so reads never parse the document, and the planner sees a normal column predicate. That works in PostgreSQL, MySQL, SQL Server, Oracle and SQLite, with MySQL's VIRTUAL generated columns being especially good because they cost no storage and are still indexable. An expression index directly on json_extract is the alternative, with the caveat that the predicate has to match the indexed expression exactly — indexing json_extract(doc,'$.type') and then querying doc ->> '$.type' misses the index even though they mean the same thing. Where the queried paths are open-ended rather than known in advance, PostgreSQL's GIN index over jsonb is the tool, and the trap there is that GIN supports containment and key existence but not path extraction, so WHERE doc ->> 'type' = 'x' will not use it — you need WHERE doc @> '{"type":"x"}'. And I would confirm with EXPLAIN either way, because a JSON index that never gets chosen is pure write overhead.
From the lesson Indexing JSON Data
27. Does CHECK (discount <= price) guarantee the discount never exceeds the price?
No, and this is the trap worth knowing. A CHECK rejects a row only when the expression evaluates to false. Any comparison involving NULL evaluates to UNKNOWN, and UNKNOWN is not false, so the row is accepted. That means a NULL discount passes, a NULL price passes, and a row with a NULL price and a discount of five hundred passes — which is absurd data that the constraint was written to prevent. The fix is to say what you mean about absence: either make both columns NOT NULL, so the UNKNOWN case cannot arise, or write it in — CHECK (discount IS NULL OR discount <= price) if absent is allowed, CHECK (discount IS NOT NULL AND discount <= price) if it is not. More generally NOT NULL and CHECK are different tools and you usually need both. I would also mention that a CHECK sees only its own row: no subqueries, no other tables, no other rows. Cross-table rules are foreign keys, and cross-row rules need a trigger or, in PostgreSQL, an exclusion constraint.
From the lesson Advanced CHECK Constraints
28. When would you need a deferred constraint?
When the end state is valid but no single ordering of statements reaches it. The two standard cases are circular references — two tables that each require a row in the other, so whichever you insert into first violates its foreign key — and swapping two values that share a unique constraint, where either update collides with the row you have not moved yet. Deferring moves the check from the end of each statement to COMMIT, by which point the transaction as a whole is consistent. Three things I would be careful about. Deferral moves the check, it does not remove it: an unresolved constraint fails at COMMIT and takes the entire transaction with it, and the error arrives a long way from its cause, which makes it harder to debug. I would defer only the constraints that need it rather than declaring everything deferrable, because violations are then found after all the work is done. And support is uneven: PostgreSQL cannot defer CHECK or NOT NULL, only foreign keys, unique and exclusion constraints; Oracle can defer everything; SQL Server and MySQL have no deferral at all. MySQL's FOREIGN_KEY_CHECKS = 0 is not an equivalent and is genuinely dangerous, because turning it back on validates nothing.
From the lesson Deferred Constraints
29. What does a composite foreign key do when one of its columns is NULL?
Under MATCH SIMPLE, which is the default everywhere, the constraint is satisfied without checking anything at all. So a child row with a valid tenant id and a NULL project id is accepted even though no such parent exists — half a reference, silently allowed. That is almost never what anyone intends. MATCH FULL is the rule people assume they have: either all the referencing columns are NULL or none of them. PostgreSQL implements it; SQLite and MySQL parse it and ignore it; SQL Server and Oracle do not accept it. So the portable answer is a CHECK constraint asserting all-or-nothing, which I would write even on PostgreSQL because it states the intent in the schema where a reader will see it. Two other things about composite keys. The columns are matched positionally rather than by name, so transposing two columns of the same type compiles cleanly and references something completely different. And the child's foreign-key columns need their own index in the same order — MySQL's InnoDB creates it for you, PostgreSQL, SQL Server and Oracle do not, and without it every parent delete scans the child table under a lock.
From the lesson Composite Foreign Keys
30. How do you model two tables that reference each other?
First I would check which shape it is. A self-reference — an employee's manager, a category's parent — is easy and common: the column must be nullable, because the root of the hierarchy has no parent, and a recursive CTE walks it. Mutual reference between two tables is the harder one, where a department has a head who is an employee and every employee belongs to a department, so neither row can be inserted first. Three answers. Make one side nullable and fill it in with a second statement — this works on every engine and is usually honest, because a newly created department genuinely has no head yet. Defer the constraints so both columns can stay NOT NULL and the checks happen at COMMIT — cleaner in principle, unavailable in SQL Server and MySQL. Or remove the cycle: often 'the head of a department' is really a role, and moving it into a link table eliminates the cycle and lets the model express two heads, no head, and a history of who held it. I would usually reach for the first or third. One thing worth saying about self-references: the foreign key does not prevent cycles — it only checks the referenced row exists, so two rows can be each other's manager, and a recursive query over that runs until something stops it. If cycles are impossible in the domain, the check belongs on the write path.
From the lesson Circular Relationships
Learn it, then drill it
The SQL course teaches every one of these in context, with runnable examples. For drilling, the practice bank has 2,701 multiple-choice SQL questions with explanations.