[Feature] Add distinct parameter to relationships test

### Is this your first time submitting a feature request?

- [X] I have read the [expectations for open source contributors](https://docs.getdbt.com/docs/contributing/oss-expectations)
- [X] I have searched the existing issues, and I could not find an existing issue for this feature
- [X] I am requesting a straightforward extension of existing dbt-adapter functionality, rather than a Big Idea better suited to a discussion

### Describe the feature

It's always struck me as weird that the [relationships test](dbt/include/global_project/macros/generic_test_sql/relationships.sql) doesn't do a SELECT DISTINCT out of the box. It seems to me like the sensible default behaviour as there's no reason to assume the destination column is unique. My team are so used to copy & pasting relationship test output and inserting distinct and running it again to better identify what foreign key is missing that the only thing stopping us raising this issue years ago was bad habits!

As it happens, I think that tests should never perform grouping/reduction because it undermines should_store_failures() but [that's a separate point that hasn't gained much traction](https://github.com/dbt-labs/dbt-core/discussions/8479) (likely because few folk are using the test outputs in automated manner).

However, given this precedent isn't set, perhaps adding a distinct=false parameter which we could pass true to would be a simple backwards-compatible improvement to this test?

### Describe alternatives you've considered

A test from dbt_expectations but I don't think there is one

### Who will this benefit?

I would imagine the vast majority of relationship test users!

### Are you interested in contributing this feature?

Sure!

### Anything else?

_No response_