Which constraints automatically create an index, and which ones do not?
Two of them make an index for you, and one of them does not, and the one that does not is the one that surprises people.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
email VARCHAR(200) NOT NULL UNIQUE,
name VARCHAR(120) NOT NULL
);
Here is the list.
A primary key always has an index. That is how the engine finds the row when you look it up by its key.
A unique rule always has an index as well, because the engine has to be able to check quickly whether the value is already there.
A foreign key does not get an index automatically. And this is the one that catches people, because the engine uses the index of the parent to check the link when a row is written, so it looks like the child must have one too. It does not. The join that reads the child table is the one that needs the index, and that is the one that you have to add yourself.
And NOT NULL and a check rule make no index at all, because they say nothing about how to find a row.
constraint makes an index?
---------------- -----------------
PRIMARY KEY yes, always
UNIQUE yes, always
FOREIGN KEY no, add it yourself
NOT NULL no
CHECK no
So the rule to remember is this. The primary key and the unique rules make their own indexes. A foreign key does not, so you add an index on the child column, and that is what makes the join and the check fast.
The next question in this topic shows the indexes that a table really has.