MySQL

The UNIQUE constraint in MySQL ensures that all values in a column or a combination of columns are unique across the table. This constraint helps maintain data integrity by preventing duplicate entries in specified columns. It’s especially useful for columns that require unique identifiers or distinct values, such as email addresses or employee IDs. Below is a guide on using the UNIQUE constraint in MySQL, with code samples for tables related to a company database, including company, employees, departments, and branches.

Understanding the UNIQUE Constraints

1. Purpose
   • The UNIQUE constraint enforces the uniqueness of values in a column or a set of columns.
   • Prevents duplicate values in the specified column(s), ensuring each record is distinct based on these fields.
2. Syntax
   • The UNIQUE constraint can be defined on a single column or a combination of columns.
   • The syntax is COLUMN_NAME DATA_TYPE UNIQUE or UNIQUE (COLUMN1, COLUMN2, ...).

Code Samples

1. Creating the company Table with a UNIQUE Constraint

   CREATE TABLE company (
     company_id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(100) NOT NULL UNIQUE,
     established YEAR NOT NULL,
     revenue DECIMAL(15, 2) DEFAULT 0.00
   );

   • company_id: Unique identifier for each company (auto-incremented).
   • name: Name of the company (must be unique; no two companies can have the same name).
   • established: Year when the company was established (cannot be NULL).
   • revenue: Annual revenue (defaults to 0.00 if not specified).
2. Creating the employees Table with UNIQUE Constraints

   CREATE TABLE employees (
     employee_id INT AUTO_INCREMENT PRIMARY KEY,
     first_name VARCHAR(50) NOT NULL,
     last_name VARCHAR(50) NOT NULL,
     email VARCHAR(100) UNIQUE NOT NULL,
     salary DECIMAL(10, 2) DEFAULT 30000.00,
     hire_date DATE DEFAULT CURDATE(),
     department_id INT DEFAULT NULL,
     status VARCHAR(10) DEFAULT 'Active'
   );

   • employee_id: Unique identifier for each employee (auto-incremented).
   • first_name and last_name: Required fields (cannot be NULL).
   • email: Employee’s email address (must be unique; no two employees can have the same email).
   • salary: Salary of the employee (defaults to 30,000.00 if not specified).
   • hire_date: Date the employee was hired (defaults to the current date if not specified).
   • department_id: Reference to the department (can be NULL).
   • status: Employment status (defaults to 'Active').
3. Creating the departments Table with a UNIQUE Constraint

   CREATE TABLE departments (
     department_id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(100) NOT NULL,
     budget DECIMAL(12, 2) DEFAULT 100000.00,
     num_employees INT DEFAULT 0
   );

   • department_id: Unique identifier for each department (auto-incremented).
   • name: Name of the department (must be unique; no two departments can have the same name).
   • budget: Budget allocated to the department (defaults to 100,000.00 if not specified).
   • num_employees: Number of employees in the department (defaults to NULL if not specified).
4. Creating the branches Table with UNIQUE Constraints

   CREATE TABLE branches (
     branch_id INT AUTO_INCREMENT PRIMARY KEY,
     address VARCHAR(255) NOT NULL,
     city VARCHAR(100) NOT NULL,
     state VARCHAR(100) NOT NULL,
     postal_code VARCHAR(20) UNIQUE NOT NULL,
     opening_hours VARCHAR(50) DEFAULT '9am-5pm'
   );

   • branch_id: Unique identifier for each branch (auto-incremented).
   • address, city, and state: Required fields (cannot be NULL).
   • postal_code: Postal code (must be unique; no two branches can have the same postal code).
   • opening_hours: Description of the branch's opening hours (defaults to '9am-5pm').

Important Considerations

• Composite Unique Constraints: You can enforce uniqueness across multiple columns by defining a composite unique constraint.

  UNIQUE (column1, column2)

• Error Handling: Inserting duplicate values in a column with a UNIQUE constraint will result in an error.
• Index Creation: When you define a UNIQUE constraint, MySQL automatically creates a unique index on the column(s) to enforce this constraint.

By using the UNIQUE constraint as demonstrated in these examples, beginners can ensure that their database tables maintain unique records and adhere to data integrity rules in MySQL.

To gain complete access, login with gmail or outlook, no need of signup, click here