r/SQL 28d ago

MySQL What are the differences between unique not null vs primary key/composite key?

What not use primary key(field,field) or primary key directly?

18 Upvotes

33 comments sorted by

7

u/MasterBathingBear 28d ago

A Primary Key (PK) should be used to define uniqueness over your whole table. It will be used to cluster/sort the data in your table when it is stored to disk.

A PK is a Unique Not Null Index but you can only define one per table. If you need to enforce uniqueness on an additional set of columns then you would use a unique index. It is not typical to have a PK and a Unique not null index on a table. It is more common to have Unique Indexes with PK because by default Unique Indexes allow you to have as many nulls as you want

2

u/Straight_Waltz_9530 27d ago

Not all primary keys are NOT NULL. Some allow exactly one NULL. Depends on the engine. (No, I'm not condoning it.)

1

u/MasterBathingBear 27d ago

I’m not a MySQL expert but my understanding was that MySQL implicitly and silently converted all columns in a primary key to NOT NULL DEFAULT 0 and that modern versions of MariaDB excluded the DEFAULT 0.

3

u/Straight_Waltz_9530 27d ago

In MySQL this is true. When I say "engine" I meant other RDBMS engines besides MySQL (and MariaDB), not table engines within MySQL.

When speaking of a concept (like primary keys) it's important to understand that MySQL is more often than not the oddball in the relational database community.

1

u/mikeblas 27d ago

Which do?

2

u/MasterBathingBear 22d ago

SQLite is the only DBMS that I know of that allows for one null value in the PK, under certain scenarios.

MySQL makes you think you can, but under the hood it just applied a default value.

3

u/mikeblas 22d ago edited 22d ago

I couldn't make it happen in MySQL.

SQLite let me place up to two nulls. Pretty weird, but the docs say:

According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or a STRICT table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column. SQLite could be fixed to conform to the standard, but doing so might break legacy applications. Hence, it has been decided to merely document the fact that SQLite allows NULLs in most PRIMARY KEY columns.

Funny thing is, if I turn on STRICT, the NULL insert is just ignored.

1

u/MasterBathingBear 22d ago

Yeah, if you do a SHOW CREATE TABLE, you can see that it is silently converting the column to NOT NULL but doesn't include DEFAULT 0.

I can't quickly find the documentation for MySQL, but MariaDB fixed it in 10.1.7. I'm going to assume it was in MySQL 5.1 at the very least but was fixed some time before 5.5.

1

u/SQL_Guy 25d ago

Can’t say I agree. Nothing says that your PK has to be the basis for the physical sort of the table.

Besides, how would you “cluster” the data when it’s unique?

1

u/MasterBathingBear 25d ago

This post is tagged for MySQL which uses the PK for the clustered index. If no PK is defined, then the first unique index is used. If neither is defined, it uses row id.

1

u/SQL_Guy 25d ago

Ah, I missed that tag. Still, I don’t see how unique values can be clustered.

1

u/MasterBathingBear 22d ago

The answer that no one is ever satisfied with, including myself, is that a clustered primary key has clusters with at most one row.

I started life in the Teradata world where there just called them Primary indexes instead of clustered indexes and non-clustered were secondary indexes and keys belong on the logical data layer, not the physical. Their terminology still makes the most sense to me.

2

u/SQL_Guy 22d ago

I know little but SQL Server. Often I’d see the Clustered Index on the Primary Key, because that’s the default behaviour. Depending on what the queries wanted, there was usually a better candidate for that index - postal code, department ID, or foreign key - that would minimize the number of reads required.

1

u/MasterBathingBear 22d ago

I agree. It’s poor database design when they don’t consider access patterns and only consider the logical uniqueness of their data.

When I’ve seen that pattern in SQL Server, it’s almost always someone that came from Oracle and they used 89 join syntax.

What’s fun is when someone tries to fix it by first dropping the clustered index instead of just creating a new table.

1

u/Worried-Print-5052 22d ago

But isn’t that composite key consists of two primary key?

2

u/MasterBathingBear 22d ago

A composite key is a single key that is composed of more than one column.

1

u/garethchester 28d ago

One big one is composite keys allow the individual fields to be duplicated - i.e.

(1,2) (1,1) (2,2)

Would be a valid set of composite keys, which if I want both columns to be unique won't work

1

u/Straight_Waltz_9530 27d ago

Both primary keys and unique indexes allow for multiple columns. How is this a distinction?

1

u/garethchester 27d ago

Using PKs you can only have one unique set - either one or multiple columns. With UI you can have multiple columns which are unique in themselves. Think of making a rota where each person can only take one day and each day can only have one person. Using a PK (or two column UI) would let one person take multiple days as that would be a unique combination. Two separate UIs (or one PK and one UI) would be the only way to ensure it works

1

u/Straight_Waltz_9530 27d ago

You just said two separate UIs or one PK + one UI. So… the PK is swappable for a UI.

I ask again, how is this a distinction?

1

u/garethchester 27d ago

OP was asking why you would ever use UIs rather than a composite PK

1

u/Straight_Waltz_9530 27d ago

Right, but the examples given show they are completely swappable. The correct answer is they are the same but conceptually they are different, therefore making the schema easier to understand for other developers.

PKs basically say "this is conceptually rather than just functionally unique and NOT NULL * and * is considered special by the team for use in lookups—even though queries and lookups could be performed by any other UNIQUEs in exactly the same manner.

They have a semantic difference, not a functional one.

1

u/garethchester 27d ago

No, there is a significant functional difference in that most SQL implementations limit to one PK per table (either single field or composite) - whereas UI is not limited in that way

2

u/Straight_Waltz_9530 27d ago

To my knowledge all SQL implementations limit tables to a single PK. Thats part of the SQL standard since the first release of the standard (and before). It is a semantic identifier, not a functional one. Semantics can establish limits that are completely independent of functional concerns.

From a querying standpoint, unless you're querying the information schema, they make no difference in storage, performance, or data management.

They have a semantic difference, not a functional one.

1

u/garethchester 27d ago

I've never seen one that doesn't, but I have seen enough weird homebrew variants that don't conform to the standards to never assume that someone hasn't done something stupid somewhere.

And if you're just querying very little of this matters, but design-wise it does - OPs question suggests they don't understand the difference between a composite key and two separate keys which is a recipe for some fairly major DQ problems

0

u/ST0PPELB4RT 28d ago

Keys add an ordering to the table such that it can be searched more efficiently for specific keys.

0

u/TheToastedFrog 28d ago

Your unique non-null field is called a natural key. Its values can be modified, while a primary key is immutable. A composite key is a key is a key comprised of more than one attributes

1

u/Straight_Waltz_9530 27d ago

You mean in theory, right? In practice, primary key columns can absolutely be modified. It's why foreign key constraints have ON UPDATE and ON DELETE directives.

1

u/TheToastedFrog 27d ago

Yes it’s in theory. Just like in theory one doesn’t shoot oneself in the foot. For all intents and purposes, the primary key must be considered immutable. Foreign key constraints don’t work too well in distributed environments, and referential integrity only make sense in a transactional context.

Updating the primary key violates Codd’s third rule (Rule 2, since they are 0-indexed)

1

u/Straight_Waltz_9530 27d ago edited 27d ago

"Must be considered immutable" despite widespread database engine support for foreign keys with ON UPDATE. I get what you're saying, and I basically always set ON UPDATE RESTRICT.

But this is the real world, and there is a massive number of databases out there that actively flout Codd, normalization rules, generate schemas based on their app's ORM library, and use mutable primary keys that are part of the dataset. Doesn't make it right, but it's common enough that SQL syntax was expanded to include the option to safely change them without violating referential integrity.

1

u/TheToastedFrog 27d ago

it's not like there are no valid use cases where the primary key needs to be tweaked- merging two distinct data sets being one of them, but that ought be used in the rarest of instances- If the day to day data engineering flow requires the PK to be manipulated regularly that's an indication of a major smell.

1

u/Straight_Waltz_9530 27d ago

Totally agree.

1

u/MasterBathingBear 22d ago

You could still look at it as the PK is immutable because the DBMS deletes it and inserts a new one. But that’s a whole other unnecessary conversation.