Blog

How to Find All The Tables Without Primary Key — Spring Boot

  • Post last modified:December 31, 2023
  • Reading time:4 mins read

Using information_schema to list tables without primary key

Tables without primary key

Introduction

  • The primary key is important since it makes each record/row in the database table unique. 
  • Other than being unique, it has other use cases such as enforcing relationships between the tables, using them as an index for the table, and supporting database operations such as insert/delete/update.
  • Although the primary key is useful but its not mandatory to define one. But its recommended to create a primary key.
  • In this article, we will write a logic that will check tables with missing primary and log the error when it finds one.

Sample Table

  • First lets create a table without a primary key.
postgres=# CREATE TABLE employees (                                                                                                                               employee_id SERIAL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

Logic

  • Our logic is to use information_schema and query for all the tables in public schema since its where i am creating tables.
  • Then we will query all the tables that have PRIMARY KEY constraints.
  • Using above two information we can find all the tables that are missing the PRIMARY KEY constraints.

Information_schema

  • Information_schema is standarized schema defined in SQL database. 
    Its designed to provide metadata and strucutral information about database objects, schema etc.

fetching all tables

  • Now lets fetch all the query tables in public schema using `Information_schema.tables` 

fetching tables without primary key

  • We can query table_constraints table where contraints_type is PRIMARY KEY. 
  • Then we can use this list to find out which of the tables in the ‘public’ schema do not present the PRIMARY KEY list.

Querying using jdbcTemplate

  • Inside the spring boot app service component, we can use jdbcTemplate to execute this query on the database.


MissingPrimaryKeyChecker Job 

  • We will execute our query and log an error if there is any missing primary key table.
  • To run this job on schedule, we can use the spring scheduler. 

Configuring App 

  • To configure our app with spring scheduler , we need to enable it using @EnableScheduling .
@SpringBootApplication
@EnableScheduling
public class PrimaryKeyCheckerApplication {

  public static void main(String[] args) {
    SpringApplication.run(PrimaryKeyCheckerApplication.class, args);
  }
}

Console Logs

  • mvn spring-boot:run : use this command to run spring boot app. 
  • Let check the console log for our schedule job:
  • As we can see, our logs getting logged in console since we have added one table employees without primary key.

Testing

  • Lets add junit test to confirm if our logic works. 

Setup 

  • Since our code queries the database, we need to setup database for our test environment.
  • We can add h2 in-memory database in test scope and provide application.properties in test folder.
  <dependency>
   <groupId>com.h2database</groupId>
   <artifactId>h2</artifactId>
   <scope>test</scope>
  </dependency>
  • Add application.properties file under src/test/resources/ . This file contains h2 database connection.
jdbc.driverClassName=org.h2.Driver
jdbc.url=jdbc:h2:mem:myDb

hibernate.dialect=org.hibernate.dialect.H2Dialect
hibernate.hbm2ddl.auto=none
  • First, lets add couple of tables before running test and delete them once we finish it.
  • Creating test tables one without the primary key and the other with a primary key.
  • deleting all the test tables.

Writing test to confirm

  • if the logs get printed when there is a missing primary key table.
  • if the logic returns primary key missing tables.

Source Code

Conclusion

  • In this article, We learned about importance of primary key in the database. We learned about information_schema which provides metadata about database objects.We created a scheduler to track the missing primary key tables. We added unit test to test our logic.

Before You Leave

Checkout interview helping contents:

Tags: , , ,