1

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?

2 Answers 2

0

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.

1
  • Yup you're right, figured that out shortly after posting but thank you for your response all the same! Feb 28 at 16:45
0

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 )                                                                                                  
);                                                                                                                             

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.