MySQL Constraints

Introduction To MySQL Constraints

The following article provides an outline for MySQL Constraints. MySQL constraint is mainly used to restrict or allow, the value or type of data, which is to be stored in the table of a database. It is the best way to maintain a database’s uprightness. One can apply constraints in two levels, one is ‘table label’ and another is ‘column level’. Column level can be applied to only one column, unlike table level which can be applied to the entire table. Once the constraints applied in the table, while executing, if there are any mismatched data entered, it will not allow executing the operation.

Different Constraints Used In MySQL

Given below are the six constraints mostly used in MySQL:

• ‘NOT NULL’
• ‘UNIQUE’
• ‘CHECK’
• ‘DEFAULT’
• ‘PRIMARY KEY’
• ‘FOREIGN KEY’

1. ‘NOT NULL’ Constraint

If we use this constraint in a column, then it will not accept any valueless entry.

For example, we have created a table called ‘Employee’, wherein we are supposed to enter the employee’s ‘first name’ and the name of the ‘hometown’ of that employee.

Code:

MySQL>CREATE TABLE Employee(Id INTEGER, Name TEXT NOT NULL, Hometown VARCHAR(40));

Now we will add some data to the table in the following way:

Code:

Mysql>INSERT INTO Employee VALUES(1,'John','Mumbai');

Mysql>INSERT INTO Employee VALUES(2,NULL,'Delhi');

The first command is correct, and it will run successfully, where we have inserted all the values. But while running the second command, it will show an error. The ‘Error 1038’ error would appear, stating column ‘Name’ cannot be null.

2. ‘UNIQUE’ Constraint

The ‘unique’ name comes from the uniqueness, which this constraint maintains in a column. The function of this constraint is, not to allow any duplicate value in a column of a table. In the table called ‘Employee’, we will apply a ‘unique’ constraint to the employee’s hometown.

Code:

MySQL> CREATE TABLE Employee(Id INTEGER, Name UNIQUE, Hometown VARCHAR(40));

Now we will add some similar data to the table in the following way:

Code:

Mysql>INSERT INTO Employee VALUES(1,'John','Mumbai');

Mysql>INSERT INTO Employee VALUES(2,'Jack','Mumbai');

The first command will run successfully where we have inserted all the values for the first time, with no repetition. But while running the second command it will show an error, as the ‘hometown’ name is the same. As per the condition, it should be different. The ‘Error 1062’ error would appear stating that it is a duplicate entry as ‘Mumbai’ for ‘Employee.Hometown’.

3. ‘CHECK’ Constraint

Here we can apply conditions or place controls to have the desired value in that column. It tries to validate the value with the given logical expression to that particular column. In the table named ‘Employee’, we have added one more column called ‘age’. Now we don’t want, that somebody enters numeric figures below ’21’.

Code:

MySQL>CREATE TABLE Employee(Id INTEGER, Name UNIQUE, HomeTown VARCHAR(40), Age int CHECK (Age>=21));

Now we will insert some value to the table:

Code:

Mysql>INSERT INTO Employee VALUES(1,'John','Mumbai',25);

Mysql>INSERT INTO Employee VALUES(2,'Jack','Delhi',15);

The second command is incorrect. It will show an error, as the age of the employee is less than 21. ‘Error 3819’ error should appear stating that ‘Employee_age_1’ has been violated.

4. ‘DEFAULT’ Constraint

In a column of a table, if we require a value, even including a NULL, then we use this constraint. In a column, if no value is inserted then it takes the default value assigned for that column. In the table named ‘Employee’, we don’t want, that somebody keeps the hometown column blank.

Code:

MySQL>CREATE TABLE Employee(Id INTEGER, Name UNIQUE, Hometown VARCHAR(40) DEFAULT'Mumbai');

Now we will add some similar data to the table in the following way:

Code:

mysql>INSERT INTO Employee VALUES(1,'John','Mumbai');

mysql>INSERT INTO Employee VALUES(2,'Jack','70');

The second command is incorrect, while running, it will not accept ’70’ as hometown instead it will take ‘Mumbai’ as a default value for that column.

5. PRIMARY KEY Constraint

To access the table faster, some unique indexing is required for that value. This constraint solves this problem very efficiently. There can be many columns in a table, containing various sets of values, but it should contain one primary key only. In the table named ‘Employee’, if someone enters the same ID repeatedly it can be a mess in the table. That is why a primary key is required.

Code:

MySQL>CREATE TABLE Employee(Id INTEGER, Name UNIQUE, Hometown VARCHAR(40) DEFAULT ‘Mumbai');

Now we will add some similar data to the table in the following way:

Code:

MySQL>INSERT INTO Employee VALUES (1,'John','Mumbai');

MySQL>INSERT INTO Employee VALUES (2,'Jack','Delhi');

MySQL>INSERT INTO Employee VALUES (2,'Jemmy', 'Kolkata');

The first and second command is correct but the third is not. While running the third command, it will not accept ‘2’ as ID. The ‘Error 1062’ error would reflect stating that it has a duplicate entry ‘2’ for the key ‘Employee.PRIMARY’.

6. FOREIGN KEY Constraint

This key is also known as the referencing key. Suppose two tables are containing different values in their respective columns. Now if we want to link these two tables, there should be something common in between, which is the primary key of both the table. Now if we try to connect or link the primary key of the first table with the primary key of the second table, then we will call the second table’s primary key a foreign key.

For example, we have created two tables, one is ‘Employee’ and another is ‘Organization’.

Code:

MySQL>CREATE TABLE Employee (Person Id PRIMARY KEY, Name UNIQUE, HomeTown VARCHAR(40));

Let’s add some data to the table:

Code:

Mysql>INSERT INTO Employee VALUES (1,'John','Mumbai');

Mysql>INSERT INTO Employee VALUES (2,'Jack','Delhi');

Mysql>INSERT INTO Employee VALUES (3,'Jimmy', 'Kolkata');

Let’s create an ‘Organization’ table:

Code:

MySQL>CREATE TABLE Organization (PersonId, Department UNIQUE, Specializations VARCHAR (40) FOREIGN KEY (Employee_ID) REFERENCES Employee(Person_ID));

We will add some data to this table too:

MySQL>INSERT INTO Employee VALUES (3,'Operations','Management');

MySQL>INSERT INTO Employee VALUES (2,'Software Development’,‘Data Science');

MySQL>INSERT INTO Employee VALUES (1,'Human Resource’,‘Hiring');

After running all these six commands this is how the details would appear:

Code:

PersonID1, Name 'John', Hometown 'Mumbai' EmployeeID1, Department 'Human Resource', Specializations 'Hiring'

PersonID2, Name 'Jack', Hometown 'Delhi'); EmployeeID2, Department 'Software Development', Specializations 'Data Science'

PersonID3, Name 'Jimmy', Hometown 'Kolkata' EmployeeID3, Department 'Operations, Specializations 'Management'

Recommended Articles

This is a guide to MySQL Constraints. Here we discuss the introduction and different constraints used in MySQL respectively. You can also go through our other suggested articles to learn more –

1. What is MySQL?
2. MySQL Operators
3. MySQL Server
4. MySQL Commands