Composite primary keys and influence on natural/surrogate keys usage

I'd go for option 2. Keep it simple.

It ticks the boxes (narrow, numeric, unchanging, strictly monotonically increasing) for a useful clustered index (which is the default of PKs in SQL Server).

You need to force the uniqueness on A,B,C,D, though, to preserve data integrity, as noted.

There is nothing conceptually wrong with option 1, but as soon as you require more indexes on "master" then the wide clustered key becomes a liability. Or more work to determine which index is best as clustered.

Edit:

In case of any confusion

the choice of which index is clustered is separate to the choice of key


Your assumption (3) tends to suggest option (2) because it is inconvenient and potentially time consuming to deal with cascading updates of the primary key of Master when B changes.

Of course it depends on how often this will occur: if it is something that you expect to happen "all the time" then it suggests (A,B,C,D) is a poor choice of primary key; on the other hand, if it will only rarely happen, then (A,B,C,D) may be a good choice of primary key, and having those columns in Slave may have some advantages (no need to join to Master all the time to find out those column values).


Either 1,2 or 3. There isn't necessarily enough information to determine whether a surrogate is necessary or how useful it might be. Are any of the compound key attributes also part of some key or constraint in the Slave table? Is there some other key of Master that could be used as a foreign key? The fact that a key value may change shouldn't be the deciding factor because any key value may need to change - surrogates are no exception.

there is quite an open debate on the topic

Unfortunately, much of that debate is based on the mistaken assumption that you need to choose between either a surrogate or a natural key. As your option 2 rightly suggests you can use both as the need arises. One is not a substitute for the other because simple keys and compound keys on different attributes obviously mean different things in your data model and enforce different constraints on your data.