>>In theory the above INSERT statement should throw a foreign constraint error because when it inserts the very first record (Paul, Alice) there is no record with 'Alice' primary key.<<
This is incorrect. There is only one INSERT statement, not a series of INSERT statements. It inserts the result of a query, that just happpens to be a bunch of UNION'ed together SELECT's for constants. However, there is no theoretic difference between this INSERT and something as
INSERT INTO Tab1 (col1, col2)
SELECT col3, col4
WHERE something = something_else;
In other words - the whole set of rows returned by the SELECT statement is inserted at once, not row by row.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis