Mastering MySQL Constraints: A Guide for Effective Database Management

MySQL constraints are crucial for maintaining the accuracy and reliability of data within database tables. They limit the type of data that can be entered, helping to maintain data integrity by preventing invalid entries. If a data operation violates a constraint, such as attempting to insert a NULL value into a non-NULL column, the action is aborted, which protects the database from becoming inconsistent.

Constraints are categorized as either column-level or table-level. Column-level constraints, like NOT NULL and UNIQUE, are applied to specific columns and define the rules for data within those columns. In contrast, table-level constraints, such as PRIMARY KEY and FOREIGN KEY, apply to the entire table and can involve multiple columns, ensuring proper relationships between tables are maintained. By effectively utilizing these constraints, we can build a robust and reliable MySQL database. In this article, we will explore MySQL constraints and their significance in maintaining data integrity.

UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column (or a set of columns) are distinct, effectively preventing duplicate entries in a database table.

We can apply this constraint during table creation or modify an existing table to include it later.

Syntax for Creating a Table with a UNIQUE Constraint:

CREATE TABLE Employees (
    employeeID INT PRIMARY KEY,
    employeeName VARCHAR(255),
    email VARCHAR(255) UNIQUE
);

Adding a UNIQUE Constraint to an Existing Table:

ALTER TABLE Employees
ADD CONSTRAINT UNIQUE (Email);

Errors

Attempting to insert a duplicate value into a column with a UNIQUE constraint will result in an error. For example,

INSERT INTO Employees VALUES
        (101, 'David Wilson', 'david@datasapient.com'),
        (102, 'Sam Harris', 'sam@datasapient.com'),

-- This will cause an error as david@datasapient.com already exists
INSERT INTO Employees VALUES        
      (103, 'David Garrison', 'david@datasapient.com');

In this scenario, the second INSERT statement will trigger the following error, as the email address must remain unique.

NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot have a NULL value. When this constraint is applied, every row in the table must contain a value for that column.

Syntax for Creating a Table with a NOT NULL Constraint:

CREATE TABLE Employees (
    employeeID INT PRIMARY KEY,
    employeeName VARCHAR(255) NOT NULL,
    email VARCHAR(255)
);

Adding a NOT NULL Constraint to an Existing Table:

ALTER TABLE Employees
MODIFY employeeName VARCHAR(255) NOT NULL;

Errors

Attempting to insert a NULL value into the employeeName column will through an error.

INSERT INTO Employees VALUES
        (104, NULL, 'david@datasapient.com');

However it is important to notice that we can still insert an empty string for text data types or a value of zero for integer and floating-point data types. For example,

INSERT INTO Employees VALUES
        (104, '', 'sarah@datasapient.com');
SELECT * FROM Employees;

In this case, an empty string is allowed, as it is not the same as NULL.

Check constraint

It is used to limit the values that can be inserted into a column. It ensures that all values in a column meet a specific condition, providing an additional layer of data integrity and validation.

If a check constraint is added to a column and if the condition is not meet during inserting or updating values, it will through an error.

Syntax for Creating a Table with a CHECK Constraint:

CREATE TABLE Employees (
    employeeID INT,
    employeeName VARCHAR(255),
    age INT CHECK (age >= 18),  
    salary DECIMAL(10, 2) CHECK (salary > 0)  
);

In this example, the age column has a CHECK constraint that ensures all employees are at least 18 years old, while the salary column must contain positive values.

We can assign a name to a CHECK constraint, which is especially useful if we need to delete or modify it later.

CREATE TABLE Employees (
    employeeID INT,
    employeeName VARCHAR(255),
    age INT ,  
    salary DECIMAL(10, 2),
    CONSTRAINT chk_age CHECK  (age >= 18),
    CONSTRAINT chk_salary  CHECK  (salary > 0) 
);

Adding a UNIQUE Constraint to an Existing Table:

ALTER TABLE Employees
ADD CONSTRAINT chk_age CHECK  (age >= 18);

Errors:

If we try to insert a value that violates the CHECK constraint, the database will return an error. For example,

INSERT INTO Employees VALUES
        (101, 'David Wilson',  17, 95000);

Default Constraint

The DEFAULT constraint is used to provide a default value for a column when no value is specified during an insert or update operation. This ensures that the column always has a value, even if one is not provided.

Syntax for Creating a Table with a DEFAULT Constraint:

CREATE TABLE Employees (
    employeeID INT,
    employeeName VARCHAR(255),
    age INT DEFAULT 18,  
    salary DECIMAL(10, 2) 
);

Adding a DEFAULT Constraint to an Existing Table:

ALTER TABLE Employees
ALTER COLUMN age SET DEFAULT 18;

When we insert a new record without specifying a value for age column, the default value 18 will be used.

INSERT INTO Employees (employeeID, employeeName, salary) VALUES
        (101, 'David Wilson',  95000);

In this case, David Wilson will have an age of 18 automatically assigned. However if we provide an age value, then that value will be inserted and the default value will be ignored.

INSERT INTO Employees VALUES
        (102, 'Sam Harris', 21, 75000),
        (103, 'David Garrison', 43, 95000);
SELECT * FROM Employees;

Another example of Default constraint:

CREATE TABLE Transactions (
  transaction_id INT,
  amount DECIMAL(10,2),
  transaction_time DATETIME DEFAULT NOW()
);

PRIMARY KEY constraint

The PRIMARY KEY constraint is used to uniquely identify each record in a table. It ensures that the values in the designated column are unique and cannot be NULL, making it essential for maintaining data integrity.

Syntax for Creating a Table with a PRIMARY KEY Constraint:

CREATE TABLE Employees (
    employeeID INT PRIMARY KEY,
    employeeName VARCHAR(255),
    age INT ,  
    salary DECIMAL(10, 2)
    );

Adding a PRIMARY KEY Constraint to an Existing Table:

ALTER TABLE Employees
ADD CONSTRAINT PRIMARY KEY (employeeID);

Errors

If we attempt to insert a employeeID that is already exists in the database, we will encounter an error:

    INSERT INTO Employees VALUES
        (101, 'David Wilson', 36, 95000),
        (102, 'Sam Harris', 21, 75000);
-- This will cause an error since employeeID 102 already exists
   INSERT INTO Employees VALUES
        (102, 'David Garrison', 43, 95000); 

Additionally, a PRIMARY KEY column cannot contain NULL values:

  INSERT INTO Employees VALUES
        (NULL, 'David Garrison', 43, 95000);  

Constraints on PRIMARY KEY

A table can only have one PRIMARY KEY constraint. If we try to define more than one PRIMARY KEY, an error will occur:

ALTER TABLE Employees
ADD CONSTRAINT PRIMARY KEY (employeeName);

FOREIGN KEY constraint

The FOREIGN KEY constraint is used to establish a relationship between two tables. It ensures that the value in a column (or a set of columns) in one table matches values in the primary key column of another table.

Foreign key constraints ensure referential integrity between tables by enforcing valid relationships. They enhance data consistency, simplify maintenance, and improve query performance, making databases more reliable and easier to manage.

Syntax for Creating a Table with a FOREIGN KEY Constraint:

CREATE TABLE Departments (
    departmentID INT PRIMARY KEY,
    departmentName VARCHAR(255)
);

CREATE TABLE Employees (
    employeeID INT PRIMARY KEY,
    employeeName VARCHAR(255),
    departmentID INT,
    FOREIGN KEY (departmentID) REFERENCES Departments(departmentID) 
);

Inserting Data:

INSERT INTO Departments VALUES (1, 'Sales'), 
                              (2, 'Marketing');
SELECT * FROM Departments;

INSERT INTO Employees VALUES (1001, 'David Wilson', 1),
                              (1002, 'Sam Harris',2),
                              (1003, 'David Garrison',1);
SELECT * FROM Employees;

Finding Active Foreign Keys

To find active foreign keys, navigate to the table list in the Schemas section. Under the Foreign Keys column, we will see all the foreign keys applied to the table. Currently, we have one: — employees_ibfk_1

Dropping a Foreign Key

To drop a foreign key, we can use the following syntax.

ALTER TABLE Employees
DROP FOREIGN KEY employees_ibfk_1;

The foreign key should be removed.

Adding a FOREIGN KEY Constraint to an Existing Table:

We can also assign a unique name to the foreign key using ADD CONSTRAINT.

ALTER TABLE Employees
ADD CONSTRAINT fk_department 
FOREIGN KEY (departmentID) REFERENCES Departments(departmentID);

Errors

When inserting data into the Employee table, if we try to set a departmentID that does not exist in the Departments table, an error will occur:

-- this will cause an error
INSERT INTO Employees VALUES (1004, 'Serena Harris', 10);

If we try to delete a department while employees are still linked to it, we will encounter an error:

-- This will cause an error if there are employees in that department
DELETE FROM Departments WHERE departmentID = 1;

In conclusion, understanding MySQL keys and constraints is important for building strong and reliable databases. These tools help keep our data accurate and create clear relationships between tables. By mastering these concepts, we can manage our data more effectively and ensure it meets our application’s needs.