Update adapter so temporary tables are not created when foreign keys

[bdgeise]

cannot reference permanent table from temporary table constraint

resolves # [[docs dbt-labs/docs.getdbt.com/#](https://github.com/dbt-labs/dbt-adapters/issues/644)](https://github.com/dbt-labs/dbt-adapters/issues/644)

Problem

cannot reference permanent table from temporary table constraint

Solution

Implement change from https://github.com/dbt-labs/dbt-core/pull/8768 and https://github.com/dbt-labs/dbt-core/pull/8889 for Redshift

Checklist

• [x ] I have read the contributing guide and understand what's expected of me
• [ x] I have run this code in development and it appears to resolve the stated issue
• [ x] This PR includes tests, or tests are not required/relevant for this PR
• [ ] This PR has no interface changes (e.g. macros, cli, logs, json artifacts, config files, adapter interface, etc) or this PR has already received feedback and approval from Product or DX

[bdgeise]

@dbeatty10 is there anything I'm missing to have this PR assigned and merged?

[github-actions[bot]]

This PR has been marked as Stale because it has been open with no activity as of late. If you would like the PR to remain open, please comment on the PR or else it will be closed in 7 days.