Achieving Conditional Uniqueness in MariaDB - Beyond Standard Constraints

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,  -- Standard unique constraint
  email VARCHAR(100) NOT NULL,
  is_admin BOOLEAN NOT NULL DEFAULT FALSE,
  CHECK (is_admin = TRUE OR email UNIQUE)  -- Check constraint for unique email if admin is false
);

This example:

• Creates a users table with columns for id, username, email, and is_admin.
• username has a standard unique constraint.
• A check constraint ensures email is unique only if is_admin is FALSE.

Trigger:

DELIMITER //

CREATE TRIGGER enforce_unique_product_code
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
  DECLARE existing_code INT;
  SELECT COUNT(*) INTO existing_code
  FROM products
  WHERE code = NEW.code AND category = NEW.category;

  IF existing_code > 0 AND NEW.is_main_product = FALSE THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate product code for non-main product';
  END IF;
END //

DELIMITER ;

• Creates a trigger named enforce_unique_product_code that fires before inserting a new row in the products table.
• The trigger checks if a product code already exists for the same category, but only if the new product is not marked as the main product (is_main_product = FALSE).
• If a duplicate is found, the trigger signals an error message.

Unique Index with Application Logic:

This approach doesn't involve direct SQL code, but a combination of database and application logic.

1. Create a table with a unique index on the columns you want to enforce conditional uniqueness.
2. In your application code, before inserting data, check the condition for uniqueness. You can achieve this with a separate query to the database.
3. If the condition is violated (e.g., duplicate email for non-admin user), handle the error within the application and prevent the insert.

1. Unique Constraint with NULL Values:

This approach leverages the fact that MariaDB allows NULL values in unique indexes.

• Define a new column representing the "active state" (e.g., is_active).
• Create a unique constraint on the combination of columns you want conditionally unique, but include the is_active column.
• In your application logic, set is_active to NULL for inactive entries. This allows duplicates for inactive entries while keeping active entries unique based on the other columns.

Here's an example:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  is_active BOOLEAN DEFAULT TRUE,
  UNIQUE (username, email, is_active)  -- Unique only for active users
);

Note: This method has limitations. Your application logic needs to manage the is_active flag effectively. Also, excessive NULL values can impact performance.

1. Combination of Check Constraints and Unique Indexes:

This approach combines the benefits of both:

• Create a check constraint to validate the condition for uniqueness.
• Create a unique index on the relevant columns.

While the check constraint enforces the condition, the unique index provides additional performance benefits for searches and joins involving those columns.

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NOT NULL,
  category_id INT NOT NULL,
  FOREIGN KEY (category_id) REFERENCES categories(id),
  CHECK (category_id = 1 OR code UNIQUE),  -- Unique code only for category 1
  UNIQUE (code, category_id)  -- Index for faster searches/joins
);

This approach offers better performance due to the unique index but requires managing both the check constraint and application logic to ensure data integrity.