Constraints - ( MySQL Queries )

Constraints in MySQL are used to define rules for the data in a table. Constraints can be used to enforce rules on the data, such as restricting the values that can be inserted into a column or ensuring that each row in a table is unique. In this tutorial, we'll cover the various types of constraints in MySQL and how to use them in your queries.

Types of Constraints

NOT NULL Constraint

The NOT NULL constraint is used to ensure that a column cannot have a NULL value. Here's how you can add a NOT NULL constraint to a column:

CREATE TABLE customers (
   id INT NOT NULL,
   name VARCHAR(255) NOT NULL
);

In the example above, the "id" and "name" columns have the NOT NULL constraint, which means that they cannot have NULL values.

PRIMARY KEY Constraint

The PRIMARY KEY constraint is used to uniquely identify each row in a table. Here's how you can add a PRIMARY KEY constraint to a column:

CREATE TABLE customers (
   id INT PRIMARY KEY,
   name VARCHAR(255)
);

In the example above, the "id" column is the primary key of the "customers" table. This means that each row in the table will have a unique "id" value.

FOREIGN KEY Constraint

The FOREIGN KEY constraint is used to ensure the referential integrity of the data in a table. It creates a relationship between two tables and ensures that the data in one table matches the data in another table. Here's how you can add a FOREIGN KEY constraint to a column:

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

In the example above, the "orders" table has a FOREIGN KEY constraint on the "customer_id" column. It ensures that the data in the "customer_id" column matches the data in the "id" column of the "customers" table.

UNIQUE Constraint

The UNIQUE constraint is used to ensure that the data in a column is unique. Here's how you can add a UNIQUE constraint to a column:

CREATE TABLE customers (
   id INT PRIMARY KEY,
   email VARCHAR(255) UNIQUE
);

In the example above, the "email" column has a UNIQUE constraint, which means that each row in the table can only have a unique email value.

Example

Let's say we want to create a table of employees with their IDs, names, and salaries. We can use constraints to ensure that each row in the table has a unique ID and a non-null name and salary value:

CREATE TABLE employees (
   id INT PRIMARY KEY,
   name VARCHAR(255) NOT NULL,
   salary INT NOT NULL
);

Now, we can insert data into the table:

INSERT INTO employees (id, name, salary)
VALUES (1, 'John Doe', 50000),
      (2, 'Jane Smith', 60000),
      (3, 'Bob Johnson', 70000);

Output

When we run the example code above and select all data from the "employees" table, the output will be:

+----+-------------+--------+
| id | name        | salary |
+----+-------------+--------+
| 1  | John Doe    | 50000  |
| 2  | Jane Smith  | 60000  |
| 3  | Bob Johnson | 70000  |
+----+-------------+--------+

This shows the data inserted into the "employees" table.

Explanation

In the example above, we created a table called "employees" with columns for "id", "name", and "salary". We used various constraints to ensure the data in the table is valid - the "id" column is the primary key, which means each row has a unique ID, while the "name" and "salary" column have the NOT NULL constraint, which means that they must have a value.

We then inserted data into the "employees" table using the INSERT INTO statement.

Use

Constraints in MySQL are useful for ensuring that the data in your tables is valid and meets certain conditions. They can help prevent errors and inconsistencies in your data.

Important Points

• Constraints can be used to enforce rules on the data in a table.
• The various types of constraints include NOT NULL, PRIMARY KEY, FOREIGN KEY, and UNIQUE.
• Constraints are useful for ensuring that the data in your tables is valid and meets certain conditions.

Summary

In this tutorial, we covered the various types of constraints in MySQL, including NOT NULL, PRIMARY KEY, FOREIGN KEY, and UNIQUE. We also covered how to use constraints in your queries to ensure that the data in your tables is valid. By understanding constraints, you can ensure the integrity of your data and prevent errors in your MySQL queries.