Incorrect uniqueidentifier works without a Primary Key but fails with one
I was able to repro this.

Both plans had the row from #temptable driving a nested loops join.

When #DatabaseTable has a primary key there is an index to seek into so the inside of the join has a seek. It is necessary to cast the varchar to uniqueidentifier to get the value to seek at which point the attempt to convert the invalid value fails.

When there is no primary key the plan has a scan on the inside of the nested loops instead. it can defer the evaluation until it has a row to compare, there is no row output from the scan on #DatabaseTable (as it is empty) so the CONVERT_IMPLICIT is never required to be evaluated.

The position of the compute scalar may lead you to believe otherwise but see Compute Scalars, Expressions and Execution Plan Performance for more about that.