MySQL Constraints

Each table has some rules that allow the type of data to be inserted into the table, those rules are called Constraints. Constraints make sure the integrity of the table and make sure all inserted rows fulfill the requirements. Some SQL Constraints are

A row in a table can hold a value or can be empty. the NULL keyword marks the absence of a value. The NOT NULL constraint ensures that the column always has some values.

if the table was already created, the NOT NULL Constraint can be added later by ALTER command

if the NOT NULL constraint is applied on a column that already has some null values, then SQL will give an error as shown below

The Default constraint provides a default value when the insert query doesn’t provide any value for a specified column.

if the table was already created, the DEFAULT constraint can be added later by ALTER command

If the table has a DEFAULT constraint, it can be removed later using ALTER command.

The unique constraint makes sure that all the values of the column must be unique, it doesn’t allow 2 different records to have the same value for the column.

The UNIQUE constraint can be applied to multiple columns of a single table.

if the table was created without the UNIQUE constraint, it can be added later using ALTER command

multiple columns can be combined to have a UNIQUE value for all columns combined.

The UNIQUE constraints can be dropped using ALTER command

The primary key is the column value using which the row can be referred. The primary key must always be unique within a table and must not be null.

if the table is created without a PRIMARY Key, it can be added later using ALTER command

The PRIMARY key can be added to multiple columns

if the table is created without a PRIMARY key constraint for multiple columns, it can be added later using ALTER command

The PRIMARY key constraint can be dropped using ALTER command

Data is distributed among multiple tables, so to manage the relationship among tables, the column of the first table must be connected to the column of the second table. A FOREIGN key also known as a REFERENCE Key is used to line different tables together. A PRIMARY key can be a FOREIGN key in another table.

if the table is created without a FOREIGN key reference, it can be added later using ALTER command

FOREIGN Key constraint can be dropped using ALTER command

CHECK constraint allows a condition to validate the record before inserting a row into the table. If the condition evaluates to false, the constraint will be violated and the record will not be inserted into the table.

if the table was created without a CHECK constraint, then it can be added later using ALTER command

INDEX is created in the database to search and retrieve records. The PRIMARY Key of the table is by default INDEXED. Indexes improve the performance of database execution but too many indexes will slow down as well.

INDEX can be created on multiple columns combined

INDEX can be dropped using ALTER command

Related posts:

Memory errors happened in real life, and they are several cases mentioned in news articles. The cases showed the consequences for their mistakes, and researchers revealed the reasons behind their…

Do you want to trigger functions on your server? Need to update data on your database? Here’s how you can do it in React Studio. React Studio makes it easy to load data from API to app (Data sheets)…

Writing is a profession where your ability to express information in written form comes primarily from experience. A niche, or your specialty, is generally an area where you have expertise. You can…