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 UNIQUE KEY: Unique Constraint

The UNIQUE KEY constraint in MySQL is used to ensure that all values in a column are different. Unlike the PRIMARY KEY constraint, a UNIQUE KEY column can contain NULL values, but it still cannot contain duplicate values. A table can have multiple UNIQUE KEYs.

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 UNIQUE KEY:

Suppose you want to create a users table with a username column that must always have unique values:

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

In this example, you cannot insert a new user with a username that already exists in the table.

Insert data into the table:

When you insert a new user into the users table, the username must be unique:

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

Attempt to insert a user with a duplicate username:

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

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

Change a column to UNIQUE KEY:

You can also change an existing column to UNIQUE KEY using the ALTER TABLE statement. However, you must ensure that the column does not currently contain any duplicate values, or the statement will fail:

ALTER TABLE users ADD UNIQUE (username);

After running this statement, the username column cannot have any duplicate values.

Exit the MySQL command-line client:

EXIT;

By using the UNIQUE KEY constraint in MySQL, you can ensure that a column always contains unique values. This can help to avoid duplicates and make your data more reliable.

How to Create a UNIQUE KEY in MySQL:

Use the UNIQUE KEY constraint during table creation.

CREATE TABLE products (
  product_id INT UNIQUE,
  product_name VARCHAR(255) NOT NULL
);

Adding Unique Constraints to Existing Columns in MySQL:
- Alter existing columns to add unique constraints.
```
ALTER TABLE customers
ADD UNIQUE (email);
```

MySQL UNIQUE KEY Examples:

Examples of unique keys in MySQL tables.

CREATE TABLE books (
  isbn VARCHAR(13) UNIQUE,
  title VARCHAR(255) NOT NULL
);

Composite Unique Keys in MySQL:

Use multiple columns as a composite unique key.

CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  UNIQUE (order_id, product_id),
  quantity INT
);

Changing Unique Keys in MySQL Tables:

Alter tables to modify unique keys.

ALTER TABLE products
DROP INDEX product_id,
ADD UNIQUE (product_code);

Handling NULL Values with UNIQUE KEY in MySQL:

Allow only one NULL value in a unique key.

CREATE TABLE students (
  student_id INT UNIQUE,
  student_name VARCHAR(50) NOT NULL,
  UNIQUE (student_email)
);

MySQL UNIQUE KEY and Index Creation:

Understand the relationship between unique keys and indexes.

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  UNIQUE (order_id),
  INDEX (customer_id)
);

Enforcing Uniqueness with UNIQUE KEY in MySQL:

Ensure uniqueness with the UNIQUE KEY constraint.

CREATE TABLE departments (
  department_id INT UNIQUE,
  department_name VARCHAR(50) NOT NULL
);

Defining Unique Constraints Using CREATE TABLE in MySQL:
- Specify unique constraints during table creation.
```
CREATE TABLE users (
  user_id INT,
  username VARCHAR(50) UNIQUE,
  password VARCHAR(255) NOT NULL
);
```
Disabling and Enabling UNIQUE KEY Constraints in MySQL:
- Temporarily disable and enable unique key constraints.
```
ALTER TABLE products
DISABLE INDEX product_code;
```
Checking Existing UNIQUE KEY Constraints in MySQL:
- Verify unique key constraints in MySQL.
```
SHOW INDEX FROM products WHERE Key_name = 'product_code';
```

Combining UNIQUE KEY with Other Constraints in MySQL:

Use unique keys in combination with other constraints.

CREATE TABLE invoices (
  invoice_id INT PRIMARY KEY,
  customer_id INT,
  UNIQUE (invoice_number),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);