MySQL Primary Key (Java)

Why This Matters

In this comprehensive lesson, we delve deep into the importance of using a primary key when working with databases in Java, particularly with MySQL. A primary key is essential for maintaining data integrity and ensuring efficient database management. It helps uniquely identify each record within a table and plays a crucial role in various scenarios such as searching, sorting, and joining tables.

In the realm of programming interviews, understanding the concept of primary keys can help you tackle real-world database challenges more effectively. Additionally, avoiding common mistakes when working with primary keys can prevent costly bugs in your applications.

Prerequisites

To follow this lesson, you should have a basic understanding of:

1. Java programming language syntax and data types
2. SQL (Structured Query Language) for managing databases
3. MySQL Workbench or any other MySQL client to create and manage databases
4. Familiarity with Object-Relational Mapping (ORM) libraries like Hibernate or JPA is beneficial but not required.

Core Concept

Primary Key Definition

A primary key is a unique column or set of columns in a database table that identifies each row unambiguously. In MySQL, you can define a primary key using the PRIMARY KEY keyword followed by one or more column names.

CREATE TABLE Employees (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Salary DECIMAL(10, 2)
);

In this example, the ID column is defined as the primary key for the Employees table.

Primary Key Properties

• Unique: Each value in a primary key must be unique across all rows of the table.
• Not Null: A primary key cannot contain NULL values.
• Implicit Index: A primary key is automatically indexed to improve query performance.
• Single Table: A table can have only one primary key, but each column in a composite primary key belongs to that specific table.

Using Java JDBC and ORM Libraries to Work with Primary Keys

To work with primary keys using Java's JDBC (Java Database Connectivity) API or Object-Relational Mapping (ORM) libraries like Hibernate or JPA, you'll need to establish a connection to the MySQL database and execute SQL queries. Here's an example of inserting, retrieving, updating, and deleting records using primary keys:

Using JDBC

import java.sql.*;

public class PrimaryKeyExample {
public static void main(String[] args) throws Exception {
// Load the MySQL driver
Class.forName("com.mysql.cj.jdbc.Driver");

// Establish a connection to the database
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/mydb", "username", "password");

// Create an SQL statement for inserting data
String sqlInsert = "INSERT INTO Employees (ID, Name, Age, Salary) VALUES (?, ?, ?, ?)";
PreparedStatement pstmtInsert = conn.prepareStatement(sqlInsert);

// Set values for the parameters in the SQL statement
pstmtInsert.setInt(1, 1);
pstmtInsert.setString(2, "John Doe");
pstmtInsert.setInt(3, 30);
pstmtInsert.setDouble(4, 50000.00);

// Execute the SQL statement and insert data into the table
pstmtInsert.executeUpdate();

// Retrieve all employees from the Employees table
String sqlSelectAll = "SELECT * FROM Employees";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sqlSelectAll);

// Process the retrieved data
while (rs.next()) {
System.out.println("ID: " + rs.getInt(1));
System.out.println("Name: " + rs.getString(2));
System.out.println("Age: " + rs.getInt(3));
System.out.println("Salary: " + rs.getDouble(4));
}

// Update an employee's salary using JDBC
String sqlUpdate = "UPDATE Employees SET Salary = ? WHERE ID = ?";
PreparedStatement pstmtUpdate = conn.prepareStatement(sqlUpdate);
pstmtUpdate.setDouble(1, 55000.00);
pstmtUpdate.setInt(2, 1);
pstmtUpdate.executeUpdate();

// Delete an employee with ID 1 using JDBC
String sqlDelete = "DELETE FROM Employees WHERE ID = ?";
PreparedStatement pstmtDelete = conn.prepareStatement(sqlDelete);
pstmtDelete.setInt(1, 1);
pstmtDelete.executeUpdate();

// Close the database connection
conn.close();
}
}

Using Hibernate

import org.hibernate.*;
import org.hibernate.cfg.*;

public class PrimaryKeyExample {
public static void main(String[] args) throws Exception {
Configuration cfg = new Configuration().configure();
SessionFactory factory = cfg.buildSessionFactory();
Session session = factory.openSession();

// Begin a transaction
Transaction tx = session.beginTransaction();

// Create an Employee object and set its properties
Employee employee = new Employee();
employee.setId(1);
employee.setName("John Doe");
employee.setAge(30);
employee.setSalary(50000.00);

// Save the employee using Hibernate
session.save(employee);

// Retrieve all employees from the Employees table
Query query = session.createQuery("FROM Employee");
List<Employee> employees = query.list();

for (Employee e : employees) {
System.out.println("ID: " + e.getId());
System.out.println("Name: " + e.getName());
System.out.println("Age: " + e.getAge());
System.out.println("Salary: " + e.getSalary());
}

// Update an employee's salary using Hibernate
Query updateQuery = session.createQuery("UPDATE Employee SET salary = :newSalary WHERE id = :id");
updateQuery.setParameter("newSalary", 55000.00);
updateQuery.setParameter("id", 1);
int rowsAffected = updateQuery.executeUpdate();

// Delete an employee with ID 1 using Hibernate
Query deleteQuery = session.createQuery("DELETE FROM Employee WHERE id = :id");
deleteQuery.setParameter("id", 1);
int rowsDeleted = deleteQuery.executeUpdate();

// Commit the transaction and close the session
tx.commit();
session.close();
factory.close();
}
}

Worked Example

Let's create a simple Java application that manages a library with books and authors. The Books table has a primary key of ID, while the Authors table has a primary key of AuthorID. We will use Hibernate for this example.

CREATE TABLE Authors (
AuthorID INT PRIMARY KEY,
Name VARCHAR(50),
BirthYear YEAR
);

CREATE TABLE Books (
ID INT PRIMARY KEY,
Title VARCHAR(100),
ISBN VARCHAR(13),
AuthorID INT,
FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

Here's the Java code for adding, retrieving, updating, and deleting books and authors using Hibernate:

import org.hibernate.*;
import org.hibernate.cfg.*;

public class LibraryExample {
public static void main(String[] args) throws Exception {
Configuration cfg = new Configuration().configure();
SessionFactory factory = cfg.buildSessionFactory();
Session session = factory.openSession();

// Begin a transaction
Transaction tx = session.beginTransaction();

// Create an Author object and set its properties
Author author = new Author();
author.setAuthorID(1);
author.setName("John Doe");
author.setBirthYear(1980);
session.save(author);

// Create a Book object and set its properties
Book book = new Book();
book.setId(1);
book.setTitle("The Catcher in the Rye");
book.setIsbn("9780449136550");
book.setAuthorID(1); // AuthorID is the ID of the author we just inserted
session.save(book);

// Retrieve all books and their authors
Query query = session.createQuery("FROM Book JOIN FETCH Book.author");
List<Book> books = query.list();

for (Book b : books) {
System.out.println("Title: " + b.getTitle());
System.out.println("Author: " + b.getAuthor().getName());
}

// Update an author's birth year using Hibernate
Query updateQuery = session.createQuery("UPDATE Author SET birthYear = :newBirthYear WHERE authorID = :id");
updateQuery.setParameter("newBirthYear", 1978);
updateQuery.setParameter("id", 1);
int rowsAffected = updateQuery.executeUpdate();

// Delete a book by its ISBN using Hibernate
Query deleteQuery = session.createQuery("DELETE FROM Book WHERE isbn = :isbn");
deleteQuery.setParameter("isbn", "9780449136550");
int rowsDeleted = deleteQuery.executeUpdate();

// Commit the transaction and close the session
tx.commit();
session.close();
factory.close();
}
}

Common Mistakes

1. Forgetting to define a primary key

When creating a table, always remember to define one or more columns as the primary key using the PRIMARY KEY keyword.

Subheadings:

• Forgetting to define primary keys in all tables
• Using auto-incrementing columns instead of defining primary keys

2. Violating uniqueness of primary keys

Ensure that each value in a primary key column(s) is unique across all rows of the table.

Subheadings:

• Inserting duplicate primary key values
• Trying to update a primary key with an existing value

3. Inserting NULL values into primary key columns

Primary key columns cannot contain NULL values. If you need to insert a new record without an ID, use an AUTO_INCREMENT column.

Subheadings:

• Forgetting to set the primary key value when inserting new records
• Allowing nullable primary keys in table definitions

4. Forgetting foreign key constraints

When working with multiple tables that are related, don't forget to define foreign key constraints to maintain data integrity.

Subheadings:

• Not specifying foreign key constraints when creating tables
• Allowing invalid foreign key values (e.g., referencing non-existing records)

Practice Questions

1. Write SQL statements to create a table named Orders with a primary key of OrderID. The Orders table should have columns for CustomerID, ProductID, and Quantity. Define the CustomerID and ProductID as foreign keys that reference the corresponding tables.
2. Write Java code to insert, retrieve, update, and delete records from the Orders table created in question 1 using JDBC or Hibernate.
3. Explain what would happen if you violate the uniqueness constraint of a primary key column.
4. How can you ensure that a foreign key column always references an existing value in its referenced table?
5. What is the purpose of an AUTO_INCREMENT column, and when would you use it?

FAQ

Q1: Can I change the primary key of a table after it has been created?

A1: No, you cannot change the primary key of a table once it has been created. Instead, you can create a new table with the desired structure and migrate data from the old table to the new one.

Q2: Can I have multiple primary keys in a single table?

A2: Yes, you can define multiple primary keys in a single table using a composite primary key. Each column in a composite primary key belongs to that specific table.

Q3: What happens if I try to insert a duplicate primary key value into a table?

A3: If you attempt to insert a duplicate primary key value, MySQL will reject the operation

Q4: How can I delete a record with a foreign key constraint when the referenced data is still needed?

A4: To delete a record with a foreign key constraint, first delete or update the related records in the referencing table to remove the foreign key reference. Then, you can delete the original record.

Q5: Can I use an auto-incrementing column as a primary key?

A5: Yes, an AUTO_INCREMENT column is often used as a primary key because it ensures uniqueness and automatically assigns values