Nope, won't work. You are assuming that the new relationship doesn't already exist, if it does you'll violate a constraint.
Yep, coming from either side you'll need to use the FK. But thats less than challenging for an RDBMS! The point is, that when you need to update the PK, you don't need to search on two fields, since you'll likely already have retrieved your autoID previously.
I'm not saying that this method is pretty, I'm just saying that programmers can deal with n:m tables in the same way as any other persistent data. A consistent autoID field in every entity makes life a lot easy for programmers, even if does take your DB from 3NF to 2NF.
Good exmple. But I prefer a different reason: Primary Keys should not have any intrinsic meaning (semantic) whatsoever at all. If you say "the PK is built from the FK of the two participitating entities", then you could have something like this:
Employee (SocSecNumber, Name....)
ProjectTeam (ProjectName, Customer...)
Meaning that your PK now contains meiningful information, it violates (IMHO) the basic tenet that a PK's meaning is only to identify a record. Now the PK contains meaning over and above its original purpose.
OK, if the PK is built from 2 other autoID fields, then the meaning is somewhat reduced, but it nevertheless confers over and above a means of identifying a record (actually the entire record is the PK).
I guess that this is a topic which we will disagree about all day
I can see your point: you don't like surrogate (technical) primary keys, you prefer identifying some existing attribute(s) which will confer uniqueness to your record. The thinking being that this is "pure", "minimalistic", "using the data", and not adding any level of data which is of no use to anyone except maybe some DBAs or programmers.
I've been there and done that, spent many painful hours rejuggling a logical schema when a new requirement killed my previous assumptions about what made a good PK way back when.
I give some leeway about using compound PKs on intersection tables. At least until I have to explain to a programming team why their elaborate, custom built code generation tools will fail when modelling n:m relationships using compound keys. Then I take the easy route: I give them an autoID, they are happy, I am happy, the database has suffered by forcing it to manage a further 4 bytes per record.
In summing up, I would like to suggest that issuing generalities "do..., don't..." whilst certainly being very useful in some cases, can lead to the creation of dogmas, stifling originality at the expense of "received wisdom".