What is the actual difference between a PRIMARY KEY and a UNIQUE constraint in SQL?
The difference is about purpose, not about strength. Both of them stop duplicates, but they are answering different questions.
A primary key says "this is the row". There is exactly one primary key per table, every row has to have one, and it can never be null. It is what a foreign key points at, and it is what you use when you want one particular row.
A unique constraint says "no two rows may have this value here". It is a business rule, such as "every account has a different email". A table can have as many of them as it needs.
CREATE TABLE accounts (
id INTEGER PRIMARY KEY, -- identifies the row; exactly one per table
email TEXT NOT NULL UNIQUE, -- a business rule; many of these are allowed
external_ref TEXT UNIQUE -- a business rule that tolerates NULLs
);
So what is the difference in practice? Four things.
The first one is the number. There is exactly one primary key per table, and there can be any number of unique constraints.
The second one is null. A primary key can never be null. A unique constraint usually allows several null values, because two rows that both have nothing in that column are not really duplicates of each other. Some engines, such as SQL Server, treat the nulls as duplicates anyway, so you have to be careful there.
The third one is the index. The primary key almost always has an index built on it, because you look rows up by it all the time. A unique constraint usually gets one as well, but that is a choice rather than a promise.
The fourth one is the purpose. The primary key identifies. The unique constraint states a rule about the business. That difference matters when you look at the schema six months later, because the name of the constraint tells you why it is there.
The next question in this topic asks about how many of each you can have.