1) Add a column to the table which holds the source system. Change the primary key of the table to a compound key including source system and source id. Then value ('System A', row 1) is distinct from ('System B', row 1).
There are a few downsides to this. If the source system were ever to revert to previous values (say a DB was restored, or a sequence reset, or a programming error or ... ) our uniqueness would be compromised.
It is generally advantageous to make keys (and hence indexes and foreign keys) narrow for performance reasons. So a one-column key is, other things being equal, preferable to a two-column key. If you're not running your hardware to its limits this is unlikely to be a problem in real life.
Similarly having the system name - a character string - in the key makes it wider. That can be mitigated by introducing a table
SourceSystem(SystemName varchar(<whatever>), SystemId smallint) and using SystemId in the actual data table. Now you have to join to the new table should you ever need the human-readable name.
The advantage is simplicity. The loading process need only hard-code a simple string (or a table look-up) and everything else remains the same. It is easy to reference back to the source since all the IDs are intact.
2) Add a surrogate key to your table. You allocate values to this key as you insert rows. This surrogate key becomes the primary key. Say Source A sent rows 1 & 2 and Source B sent rows 1 & 5 the table becomes
Advantages are we have complete control over the primary key values in our system. Whatever changes the developers of the source choose to deploy, and however their ID scheme fluctuates, it will not affect us.
Unfortunately we have no direct reference telling us which was the source for each row, if that is important in your world. That can be addressed by adding a new non-key column to hold that information.
3) Put a offset for each source. Source A's rows all get 10,000 (or 10 million, or 100 million or whatever) added, Source B's have 20,000, Source C's have 30,000 and so on. The obvious problem is that each range could be exhausted. If that happens you can move to a new interval for that source.
Option 2 - the surrogate key - is
the my preferred solution.