MySQL Tutorial

MySQL Installation and Configuration

MySQL Database Operations

Database Design

MySQL Data Types

MySQL Storage Engines

MySQL Basic Operations of Tables

MySQL Constraints

MySQL Operators

MySQL Function

MySQL Function Overview

MySQL Manipulate Table Data

MySQL View

MySQL Indexes

MySQL Stored Procedure

MySQL Trigger

MySQL Transactions

MySQL Character Set

MySQL User Management

MySQL Database Backup and Recovery

MySQL Log

MySQL Performance Optimization

MySQL PRIMARY KEY: Primary Key

The PRIMARY KEY in MySQL is a constraint that uniquely identifies each record in a database table. Primary keys must contain unique values and cannot contain NULL values. A table can have only one primary key, which may consist of single or multiple fields.

Prerequisites:

A MySQL server up and running.
Access to a MySQL user account with privileges to create and modify tables.

Tutorial:

Connect to the MySQL server:

To start the mysql command-line client, open a terminal or command prompt, and enter:

mysql -u [username] -p

Replace [username] with your MySQL username and enter your password when prompted.

Select a database:

Select the database where you want to create your table:

USE [database_name];

Replace [database_name] with the name of your database.

Create a table with a PRIMARY KEY:

Create a users table with an id column as the PRIMARY KEY:

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(255),
    PRIMARY KEY (id)
);

In this example, the id column is the PRIMARY KEY and it is an auto-incrementing integer. This means that for each new record, MySQL will automatically assign an id that is one greater than the id of the previous record.

Insert data into the table:

When you insert a new user into the users table, you don't need to specify an id because MySQL will generate it automatically:

INSERT INTO users (username) VALUES ('johndoe');

If you select this user with SELECT * FROM users;, you will see that the user has an id of 1.

Attempt to insert a user with a duplicate id:

If you try to insert a user with an id that already exists, MySQL will reject it because of the PRIMARY KEY constraint:

INSERT INTO users (id, username) VALUES (1, 'janedoe');
-- This will fail because there is already a user with id 1

Exit the MySQL command-line client:

EXIT;

By using a PRIMARY KEY in MySQL, you can ensure that each record in your table is uniquely identifiable. This is a fundamental concept in relational databases and is key to creating relationships between tables.

How to Create a PRIMARY KEY in MySQL:

Use the PRIMARY KEY constraint during table creation.

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(50) NOT NULL,
  emp_email VARCHAR(255) UNIQUE
);

Adding Primary Keys to Existing Tables in MySQL:
- Alter existing tables to add primary keys.
```
ALTER TABLE employees
ADD PRIMARY KEY (emp_id);
```

MySQL PRIMARY KEY Examples:

Examples of primary keys in MySQL tables.

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  student_name VARCHAR(50) NOT NULL
);

Benefits of Using PRIMARY KEY in MySQL:

Primary keys provide uniqueness and faster data retrieval.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  total_amount DECIMAL(10,2)
);

Composite Primary Keys in MySQL:

Use multiple columns as a composite primary key.

CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  PRIMARY KEY (order_id, product_id),
  quantity INT
);

Changing Primary Keys in MySQL Tables:

Alter tables to modify primary keys.

ALTER TABLE employees
DROP PRIMARY KEY,
ADD PRIMARY KEY (emp_id, emp_department);

Handling Auto-incremented Primary Keys in MySQL:

Use auto-increment for unique primary keys.

CREATE TABLE products (
  product_id INT PRIMARY KEY AUTO_INCREMENT,
  product_name VARCHAR(255) NOT NULL
);

MySQL PRIMARY KEY and Foreign Key Relationships:

Link tables using primary and foreign key relationships.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Enforcing Uniqueness with PRIMARY KEY in MySQL:

Ensure uniqueness with the PRIMARY KEY constraint.

CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(50) UNIQUE
);

Defining Primary Keys Using CREATE TABLE in MySQL:

Specify primary keys during table creation.

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  password VARCHAR(255) NOT NULL
);

Disabling and Enabling PRIMARY KEY Constraints in MySQL:
- Temporarily disable and enable primary key constraints.
```
ALTER TABLE employees
DISABLE PRIMARY KEY;
```
Checking Existing PRIMARY KEY Constraints in MySQL:
- Verify primary key constraints in MySQL.
```
SHOW INDEX FROM employees WHERE Key_name = 'PRIMARY';
```