0

This might be a silly question. I know two fields can be used to create a primary key. I'm wondering if it ever makes sense (has advantages) to instead create a new additional field from those two field's values and make that the primary key. My goal for this key is to make sure no duplicate records are allowed in the table (the combination of these fields are never really queried). The two original fields would remain.

A real example, I have a table of invoicing detail, the combination of the invoice_num and line_num fields are unique. The table has approx 35M rows. Should I just create a primary key using those two fields OR create a new field that is the concatenation of the two fields and make that the primary key (this concatenation would happen prior to the data being loaded into the table).

UPDATE: It appears what I'm referring to is a "surrogate key". https://towardsdatascience.com/the-difference-between-surrogate-and-primary-keys-222cf31ddb57 Surrogate keys are the ideal solution when you don’t have a primary key in your table but need a unique identifier. They are great for performance and detecting changes in historical records.

UPDATE: My question asked in a better way previously Should I create a surrogate key instead of a composite key?

thanks

Ryzone
  • 13
  • 4
  • 4
    please, for the sake of your future you or whoever needs to work with this database **don't concatenate fields that will be needed for further querying later**. Think about what would you need to to do if you want to search either by `invoice_num` or `line_num` only. So no, definitely do not concatenate. Use a composite key. – Jorge Campos May 15 '23 at 17:11
  • 1
    By the way, there are tons of questions here on SO trying to solve this exact specific problem, query concatenated fields. lol – Jorge Campos May 15 '23 at 17:12
  • 1
    The individual fields would remain individual fields. This would be an additional field. I am just trying to prevent duplicate records from being entered into the table, I would not be querying using this field. – Ryzone May 15 '23 at 18:25
  • 2
    If you make the two ***columns*** a composite primary key, duplicates are already prevented. If the existing columns are numeric, converting them to strings for concatenation would see you fail an interview with me. – MatBailie May 15 '23 at 19:01

1 Answers1

1

Should I just create a primary key using those two fields OR create a new field

Do the former ("composite"):

PRIMARY KEY(invoice_num, line_num)

Note: A PRIMARY KEY is a Unique key (at least in MySQL). The pair is prevented from having dups. Meanwhile, there can be many line_nums for one invoice and there is probably a "line_num=1" for each different invoice.

Combining two columns is clumsy, error-prone, inefficient, etc, etc.

In your case, a "surrogate" AUTO_INCREMENT id would probably be less efficient. Do not listen to those who seem to insist on having such.

Rick James
  • 135,179
  • 13
  • 127
  • 222