You don't want the SQL to fail if there is no key. You never want the SQL to fail on a target load. If it fails for any reason, it is not "load ready".

In bulk processing space, esp in Netezza, we have much power and freedom to make the final stream

"load-ready" and this would include any foreign key checks.

In ETL we would download the keys and check them before the load, peeling off any offenders.

In ELT we would likewise join the source and target tables and allow the missing keys to be our filter, peeling off any offenders.

But we don't want the SQL to do it- because this gets the SQL process involved in the row-by-row checking of data. In a bulk processing

model this is our undoing. We never want to check the data row-by-row, and in fact are unable to do it in Netezza because it is not a transactional

environment, and this theme carries to all corners of its architecture including row-by-row checks.

See the book Netezza Underground, and note rule #10 - never use the database for row-by-row data processing.

Think about what the common RDBMS database is doing when it throws an error for a failure like this - the row-by-row  overhead for the check is enormous, and the penalty for

recovery even more so. This is why in data warehousing, load operations are usually accompanied by a command to turn off constraints so that the load can go

faster. This means that the domain where the keys are checked is the same place where the rest of the data is made load-ready - in the function that is about to perform

the load.

So the short answer is - the lack of key checking in Netezza is what is practiced in data warehousing, and is exactly what we want an appliance to do.