Question 113058 in Mysql Question posted in
The official MySQL documentation can be found here.

Does MySQL database require a unique identifier?

I’m really new to databases in general but could use some advice. I have a database that has about 6000 records (and growing but not crazy amounts). I’d like to build an API so that I can retrieve a property’s price history, but have been advised I need a unique ID but I’m not so sure. Can anyone advise?

DB looks like this:

address price date_created status
Address one, main street £150,000 13/10/2022 new data
Address one, main street £140,000 16/10/2022 update data
Address two, side road £350,000 13/10/2022 new data
Tags:

2

Answers


  1. 0

    You might find an integer primary key useful, but it’s not mandatory.

    If the address is sufficient to be the unique column by which you can reference any row, then that’s fine. It’s called a "natural key." In practice, most of us have experienced that any column you think should be unique ends up not being 100% unique eventually. So a lot of developers recommend adding a "pseudokey" which has no reason not to be unique.

    I wrote a chapter called "ID Required" describing the pros and cons of pseudokeys and natural keys in my book SQL Antipatterns, Volume 1: Avoiding the Pitfalls of Database Programming.

    Login or Signup to reply.
  2. 0

    Maybe you will need to add/edit your data using a CRUD, maybe reference other tables, make foreign keys, etc. I recommend that you add a primary key. I’ve never worked on tables without a primary key.

    I can hear my database teacher in 2001: you should always have a primary key and another unique key apart from the primary one (compound or on a single column) if you don’t want your table to be just another excel sheet. And I remember that it was a mathematical demonstration of this rule with injective functions.

    Of course, you can ignore these rules, but they are best practice advice for your future self 🙂

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search