What Is The Point of a Primary Key?

Question

Why do I need to have a primary key on my database for it to function correctly? In every tutorial I read, you need to make the id key the primary key. What does the primary key do differently than the regular cells?

Answer 1

Keys are for identification and data integrity. A key defines how tuples (rows) in a table can be uniquely identified. The integrity of keys is assured because the DBMS prevents users from entering duplicate information into the table. Database users can therefore rely on the keys to identify in the real world the things recorded in the database.

A "primary" key is fundamentally no different from any other candidate key in the same table. It's just a convention used to designate one key per table as significant in some way. Usually it is the "preferred" or "most important" key of a table and the one used in foreign key references.

Answer 2

You allude to tutorials but provide no example. Consider the following two tables that have what I understand to be an "id key" (noting the question has the auto-increment tag) and similar to many found in the wild:

CREATE TABLE Books1
(
 id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, 
 isbn CHAR(13) NOT NULL
);

INSERT INTO Books1 (isbn) VALUES ('9781444727302'), 
                                 ('9781444727302'),
                                 ('9781444727302');


CREATE TABLE Books2
(
 id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, 
 isbn CHAR(13)
);

INSERT INTO Books2 (isbn) VALUES (NULL), 
                                 (NULL),
                                 (NULL);

Does either table have a usable key? I would say no. So my first answer is that PRIMARY KEY (or auto-increment) is like any tool: without discipline it can be useless or even dangerous.

--

Reading between the lines, I think the question effectively asks

The tutorials I read tell me to add an auto-increment column to every table; they also tell me every table should have a PRIMARY KEY. Why doesn't the DBMS just automatically add the auto-increment column and make it the PRIMARY KEY?

or perhaps

The tutorials I read tell me to add an auto-increment column to every table. Isn't a system auto-increment column unique by implication? If so, why do I have to tell the system it is a PRIMARY KEY?

to which my second answer is, seek out a better tutorial!