Should i use an auto-increment primary key although i already have a UNIQUE column?

user_5787704 0
Viewed 86

Is there any reason to use id int auto-increment for a primary key while a UNIQUE column (that is not a foreign key or anything) already exists in a table?

I'm reading someone else's thesis about a project that uses freebase data. Every topic in freebase is uniquely identified by an mid. (example: m.gugkl395).

But instead of using mid as the primary key in the topics table he chose to use an id (int auto-increment). So the topics table looks like this

CREATE TABLE topics (
id INT NOT NULL AUTO-INCREMENT,
mid VARCHAR(254) NOT NULL UNIQUE,
name VARCHAR(254) NOT NULL,
description VARCHAR(2048),
type VARCHAR(254) NOT NULL,
PRIMARY KEY (id)
);

I should mention that there are 3 other tables that use this id as a foreign key and that because it has to do with freebase there will probably be a lot of data in the database. Also in case it matters MySQL version 5.7.15 is being used.

freebase primary-key foreign-keys mysql database
2 Answers

Theoretically,IMHO after 26+ years from dbaseIII+, if you have a Unique key, you can use it, auto increment field just simplify the things if you are not sure about uniqueness.

Practically, Regarding actual programming and performance of the database, you need to add multiple lines of code to create the primary key value and some times require connections to the server to get the last value of the key before the new one, these plus the space used by varchar in the design of the tables may affect the performance of the solution and take more time in programming.

I hope this may help.

user_108922120

The m.gugkl395 is the RDF encoding of MID which looks like /m/gugkl395 in its native form, but the prefix is constant and the gugkl395 is a radix 32(?) encoding of an integer. The most space efficient and performant schema would be to just store that integer.

The MID encoding is described in an earlier answer of mine here: https://stackoverflow.com/a/56012791/167425

user_1674250
Related