ENH: Add validation checks for non-unique merge keys
- [x] closes #27430
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`

After implementing the suggestion in #27430 I realized it should also apply to `1:m` and `m:1` merges. When any type of `many` merge is specified, an error will be raised if the `many` side is actually unique.
Thanks @LewisDavies for the PR. The current behaviour is documented so this is an breaking API change. Would first need a deprecation.

from https://pandas.pydata.org/pandas-docs/version/1.1.0/reference/api/pandas.merge.html

> validate : str, optional
> If specified, checks if merge is of specified type.
>
> “one_to_one” or “1:1”: check if merge keys are unique in both left and right datasets.
>
> “one_to_many” or “1:m”: check if merge keys are unique in left dataset.
>
> “many_to_one” or “m:1”: check if merge keys are unique in right dataset.
>
>“many_to_many” or “m:m”: allowed, but does not result in checks.

 FWIW i'm -1 on changing this. Happy to contribute! I didn't realize the current behaviour was intentional so I understand not wanting to implement this. That said, it feels odd that if a user specifies `m:m` they won't get an error if both sides have unique keys.

With the benefit of hindsight the names could be clearer, especially for one-to-many merges. Adding `1:` & `:1` options could make this explicit but could just add confusion so I'm not sure what's best. What do you think @simonjayhawkins? > Happy to contribute! I didn't realize the current behaviour was intentional so I understand not wanting to implement this.

Thanks @LewisDavies. That's just my opinion. others may disagree so we'll leave this open for a while.

> With the benefit of hindsight the names could be clearer, especially for one-to-many merges. Adding `1:` & `:1` options could make this explicit but could just add confusion so I'm not sure what's best. What do you think @simonjayhawkins?

another alternative could be to have, say, a `check_unique` keyword parameter that accepts, say, `left`, `right` and `both`. I think something that that may also make the intention of the functionality clearer.

There is functionality currently being added to pandas, to disallow allow duplicates and propagate that property in further operations, see #28394. It could be that in the future the `validate` parameter of merge becomes redundant. @LewisDavies are you able to address @jreback comment https://github.com/pandas-dev/pandas/pull/35373#pullrequestreview-462947864 Closing as stale, @LewisDavies let us know if you'd still like to work on this