Inserting a Hyphen in between a composite primary key

Question

I have more of a conceptual question. Is there a way to model the following:

  CREATE TABLE AssetPair (
      older_asset_id INT8,
      newer_asset_id INT8,
      PRIMARY KEY (older_asset_id, '-', newer_asset_id)
  );

My issue is that older_asset_id and newer_asset_id are incrementing IDs so the following edgecases are possible:

older_asset_id: 1, newer_asset_id: 234 --> PrimaryKey:1234
// same Primary keys
older_asset_id: 12 newer_asset_id: 34 --> PrimaryKey:1234

If I was able to insert a hyphen between the two columns I'd be able to avoid this issue. Any thoughts?

Answer 1

You misunderstand how composite keys work. A composite key looks at each column individually, not concatenated.

This is all you need to enforce integrity

CREATE TABLE AssetPair (
      older_asset_id INT8,
      newer_asset_id INT8,
      PRIMARY KEY (older_asset_id, newer_asset_id)
);

You can see this in action in this fiddle.

Answer 2

You could use a computed column based on the two id fields.

create table asset_pair (                                                                                                           
  oldid int not null,                                                                                                          
  newid int not null,                                                                                                          
  compositeid string not null as ( oldid || '-' || newid ) stored,                                                             
  primary key ( compositeid )                                                                                                  
);