As Grant said, the naming issue is not the end of the world, but it can create lack of clarity which can result in confusion; but unlike Grant, I believe that such confusion can be a show stopper unless everyone involved in writing, debugging, modifying, and maintaining the code is thoroughly aware of teh issue and on their gueard against confusion and misunderstanding.

As Grant says, the problem with working with two keys is that you need two indexes on the main table, which is an overhead in both storage and execution time. However, usually that overhead is swamped by the storage and execution time savings resulting from use of the surrogate as primary key in auxiliary tables and as the target of foreign keys (there's no excuse for using a surrogate when the savigs don't match the costs, but that rarely happens). In my experience people have tended to get this right once they've understood the concepts of forign keys and auxiliary tables, and been able to avoid using surrogates for primary keys where doing so costs more than it saves.

It's bad practise in the table that ties a surrogate key to the real primary key to call the surrogate the primary key and mark the primary key with a unique constraint (and not null constraints for its components). Call the real primary key the primary key, and mark the surrogate not null and unique. You can still use the surrogate to save space in the all the usual ways: refer to it, not to the primary key, in foreign key constraints; use it, not the real primary key, as primary key in auxiliary tables that are split off from the main table because their data is rarely accessed or because only a few rows in the main table have data in this table; and of course if you are an anti-null nut you can also still use it in a similar way to arrange to have no nullable columns in your base tables without wasting space by repeating the real primary key in lots of tables. The reason having these things the right way round in the table where the surrogate is defined is quite simple: the primary key is the key (there may be several keys, but only one is primary) that is designated for an interactive user (not a piece of application code, but a real live user) to identify a row in the table; a user will only look at auxiliary tables through joins, so he gets there going via a row chosen in the main table using the real primary key and that row tells him what the surrogate key is. It's not a catastrophe to call the surrogate the primary key in the main table, but it is in conflict with the definition of a primary key for a table that is meaningful to a human being.

Some people go one step further and insist on having a separate table that holds nothing but real primary key and surrogate, and then the main table and all the auxiliary tables use the surrogate as primary key; this costs an extra join on most queries, and extra storage space because the extra table both introduces an extra copy of each surrogate and replaces one index on the original maintain table with two on the extra table, so I've never been able to see the point of doing it. I suspect that it is this extra step that has led to some people railing against the use of surrogate keys, since their other uses are all benign (except in very small databases where auxiliary tables are sometimes examined directly by the mark 1 human eyeball, and in cases where there are no auxiliary tables, very few foreign keys, and the real primary key is a single fairly small column).