Is it necessary to have an ID column if a table has a unique column in Mysql?

Question

I have two tables A,B. Both tables will have more than 1 million records.

A has two columns - id, photo_id. id is the primary key and photo_id is unique.

A needs to be referenced in B.

3 questions:

1. Can I ignore A's id and use photo_id to link the two tables?
2. Is there any benefit of using the primary column as opposed to using a unique column in B?
3. What difference will it make to have a foreign key? I probably won't use foreign key since it's not supported on the server I'm using. Will this make a significant difference when there are 1+ mil records?

Answer 1

Skip having an id-column. If you have a photo_id that is already unique you should use that instead. A primary key (in MySQL InnoDB) is automatically clustered, which means that the data is stored in the index, making for VERY efficient retrieval of an entire row if you use the primary key as reference.

To answer your questions:

1. Yes. And remove the id-column. It is an artificial key and provide no benefits over using the photo_id
2. Yes. The primary key index is clustered and makes for very efficient querying on both exact and range-queries. (i.e. select * from photos where 2 < id AND id < 10)
3. A foreign key puts a constraint on your database tables, and ensure that the data in the tables are in a consistent state. Without foreign keys you have to have some application level logic to ensure consistency.

Answer 2

I would only remove your id column if you are positive that photo_id values will never change. If multiple rows of your B table reference a specific A row and the photo_id for that row needs to be updated, you will want to be referencing the id column from your B table.