Default Database Primary, Foreign, and Unique Key Indexing

Last modified:

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?

Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.

So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!

Introduction

In this article, we are going to see what is the default database Primary, Foreign, and Unique Key indexing strategy when using Oracle, SQL Server, PostgreSQL, and MySQL.

So, if you are wondering whether one of those top relational databases creates a default index whenever you are adding a Primary Key, Foreign Key, or Unique Key constraint, then this article is going to provide you with the answer you’ve been waiting for.

Database tables

Let’s assume we have the following post and post_comment tables that form a one-to-many table relationship:

Default Index post and post_comment tables

The parent post table has two keys:

a Primary Key constraint on the id column
a Unique Key constraint on the slug column

And the child post_comment table also has two keys:

a Primary Key constraint on the id column
a Foreign Key constraint on the post_id column

Next, we are going to see what default indexing strategy each of the top four relational database systems chooses.

Default database key indexing with Oracle

On Oracle, the post and the post_comment tables are created as follows:

CREATE TABLE post (
    id number(19,0) NOT NULL, 
    slug varchar2(255 char), 
    title varchar2(255 char), 
    PRIMARY KEY (id)
)

CREATE TABLE post_comment (
    id number(19,0) NOT NULL, 
    review varchar2(255 char), 
    post_id number(19,0), 
    PRIMARY KEY (id)
)

ALTER TABLE post 
ADD CONSTRAINT UK_POST_SLUG 
UNIQUE (slug)

ALTER TABLE post_comment 
ADD CONSTRAINT FK_POST_COMMENT_POST_ID 
FOREIGN KEY (post_id) REFERENCES post

To check what database indexes have been created for these two tables, we can use the following SQL query:

SELECT
    ind.table_name AS table_name,
    ind.index_name AS index_name,
    ind_col.column_name AS column_name,
    CASE
        WHEN ind.uniqueness = 'UNIQUE' THEN 1
        WHEN ind.uniqueness = 'NONUNIQUE' THEN 0
        END AS is_unique
FROM
    sys.all_indexes ind
INNER JOIN
    sys.all_ind_columns ind_col ON
        ind.owner = ind_col.index_owner AND
        ind.index_name = ind_col.index_name
WHERE
    lower(ind.table_name) IN ('post', 'post_comment')

When running the above query, we are going to get the following index information:

| TABLE_NAME   | INDEX_NAME   | COLUMN_NAME | IS_UNIQUE |
|--------------|--------------|-------------|-----------|
| POST         | SYS_C0047948 | ID          | 1         |
| POST         | UK_POST_SLUG | SLUG        | 1         |
| POST_COMMENT | SYS_C0047950 | ID          | 1         |

Oracle creates default indexes for Primary Key and Unique Key columns.

No index is created automatically for a Foreign Key column when using Oracle.

Default database key indexing with SQL Server

When using SQL Server, the post and the post_comment tables are created using these DDL statements:

CREATE TABLE post (
    id bigint NOT NULL, 
    slug varchar(255), 
    title varchar(255), 
    PRIMARY KEY (id)
)

CREATE TABLE post_comment (
    id bigint not null, 
    review varchar(255), 
    post_id bigint, 
    PRIMARY KEY (id)
)

ALTER TABLE post 
ADD CONSTRAINT UK_POST_SLUG 
UNIQUE (slug)

ALTER TABLE post_comment 
ADD CONSTRAINT FK_POST_COMMENT_POST_ID 
FOREIGN KEY (post_id) REFERENCES post

When using SQL Server, you can use this SQL query to check what database indexes are associated with some particular tables:

SELECT
    t.name AS table_name,
    ind.name AS index_name,
    col.name AS column_name,
    ind.is_unique AS is_unique
FROM
    sys.indexes ind
INNER JOIN
    sys.index_columns ic ON
        ind.object_id = ic.object_id AND
        ind.index_id = ic.index_id
INNER JOIN
    sys.columns col ON
        ic.object_id = col.object_id AND
        ic.column_id = col.column_id
INNER JOIN
    sys.tables t ON ind.object_id = t.object_id
WHERE
    t.name IN ('post', 'post_comment')

When running the above query, we are going to get the following index information:

| table_name   | index_name                     | column_name | is_unique |
|--------------|--------------------------------|-------------|-----------|
| post         | PK__post__3213E83F82A8BE22     | id          | true      |
| post         | UK_POST_SLUG                   | slug        | true      |
| post_comment | PK__post_com__3213E83F23045CBD | id          | true      |

SQL Server creates default indexes for Primary Key and Unique Key columns.

No index is created automatically for a Foreign Key column when using SQL Server.

Default database key indexing with PostgreSQL

On PostgreSQL, the post and the post_comment tables are created like this:

CREATE TABLE post (
    id int8 NOT NULL, 
    slug varchar(255), 
    title varchar(255), PRIMARY KEY (id)
)

CREATE TABLE post_comment (
    id int8 NOT NULL, 
    review varchar(255), 
    post_id int8, 
    PRIMARY KEY (id)
)

ALTER TABLE IF EXISTS post 
ADD CONSTRAINT UK_POST_SLUG 
UNIQUE (slug)

ALTER TABLE IF EXISTS post_comment 
ADD CONSTRAINT FK_POST_COMMENT_POST_ID 
FOREIGN KEY (post_id) REFERENCES post

To verify the database indexes that were created by PostgreSQL for these two tables, we can use the following SQL query:

SELECT
    c.relname AS table_name,
    i.relname AS index_name,
    a.attname AS column_name,
    ix.indisunique AS is_unique
FROM
    pg_class c
INNER JOIN
    pg_index ix ON c.oid = ix.indrelid
INNER JOIN
    pg_class i ON ix.indexrelid = i.oid
INNER JOIN
    pg_attribute a ON 
        a.attrelid = c.oid AND 
        a.attnum = ANY(ix.indkey)
WHERE
    c.relname IN ('post', 'post_comment')
ORDER BY
    c.relname

When executing the above query, we are going to get the following index result set:

| table_name   | index_name        | column_name | is_unique |
|--------------|-------------------|-------------|-----------|
| post         | post_pkey         | id          | true      |
| post         | uk_post_slug      | slug        | true      |
| post_comment | post_comment_pkey | id          | true      |

PostgreSQL creates default indexes for Primary Key and Unique Key columns.

No index is created automatically for a Foreign Key column when using PostgreSQL.

Default database key indexing with MySQL

When using MySQL, the post and the post_comment tables are created using these DDL statements:

CREATE TABLE post (
    id bigint NOT NULL, 
    slug varchar(255), 
    title varchar(255), 
    PRIMARY KEY (id)
)

CREATE TABLE post_comment (
    id bigint NOT NULL, 
    review varchar(255), 
    post_id bigint, 
    PRIMARY KEY (id)
)

ALTER TABLE post ADD CONSTRAINT 
UK_POST_SLUG 
UNIQUE (slug)

ALTER TABLE post_comment 
ADD CONSTRAINT FK_POST_COMMENT_POST_ID 
FOREIGN KEY (post_id) REFERENCES post (id)

To check the database indexes created by default by MySQL, we can use this SQL query:

SELECT
    TABLE_NAME as table_name,
    INDEX_NAME AS index_name,
    COLUMN_NAME as column_name,
    !NON_UNIQUE AS is_unique
FROM
    INFORMATION_SCHEMA.STATISTICS
WHERE
    TABLE_NAME IN ('post', 'post_comment')

When executing the above query, we are going to get the following index result set:

| table_name   | index_name              | column_name | is_unique |
|--------------|-------------------------|-------------|-----------|
| post         | PRIMARY                 | id          | 1         |
| post         | UK_POST_SLUG            | slug        | 1         |
| post_comment | PRIMARY                 | id          | 1         |
| post_comment | FK_POST_COMMENT_POST_ID | post_id     | 0         |

MySQL creates default indexes for all Primary Key, Unique Key, and Foreign Key columns.

I'm running an online workshop on the 20-21 and 23-24 of November about High-Performance Java Persistence.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

In this article, we learned that Oracle, SQL Server, and PostgreSQL create database indexes for Primary Key and Unique Key columns, but not for Foreign Key columns.

So, if you have two large tables that have to be joined via a Foreign Key, then it would be more efficient if you created an index on the Foreign Key column. Otherwise, a table scan would be used instead. The SQL execution plan will tell you what strategy was used by the database engine, hence you can determine which Foreign Key columns need indexing.

For small tables that are joined via a Foreign Key, adding an index on the Foreign Key column might not be needed since the database Cost-Based Optimizer might do a table scan anyway.

MySQL creates default indexes for all Primary Key, Unique Key, and Foreign Key columns.

Follow @vlad_mihalcea