---
title: "SQL Constraints"  
description: "Constraints are used to apply some rules for the record in a table. If any rule is violated between constraint and data, then the action is aborted by"  
author: "Samuel Fernandes"  
published: 2016-07-01  
updated: 2018-03-16  
canonical: https://www.mindstick.com/blog/11170/sql-constraints  
category: "mysql"  
tags: ["database", "sql"]  
reading_time: 5 minutes  

---

# SQL Constraints

Constraints are used to apply some rules for the record in a table. If any rule is violated between constraint and data, then the action is aborted by the constraint. These constraints are specified when the table is created or after the table is created. Following is the **syntax** of constraint:\

```
CREATE TABLE name_of_table(
column_name1 data_type(size) name_of_table,
column_name2 data_type(size) name_of_table,
column_name3 data_type(size) name_of_table,
);
```

**Following are the types of constraints:**

**· [PRIMARY KEY](https://www.mindstick.com/blog/214/primary-key):** This key is the combination of UNIQUE KEY and NULL KEY. This key ensures that a column must have a unique identity and it cannot be left blank.

**·** **UNIQUE:** This key ensures each row must have unique value.

**· NOT NULL:** This key indicates that a column cannot be left blank.

**· CHECK:** This key checks the column meets some [specific condition](https://www.mindstick.com/forum/159295/write-a-mongodb-query-to-find-all-documents-in-a-collection-with-a-specific-condition).

**· FOREIGN KEY:** It ensures referential integrity of the record in one table to match values in another table.

**· DEFAULT:** This key specify a [default value](https://www.mindstick.com/forum/2035/default-value-when-using-singleordefault) for a column.

**NOT NULL**

The [NOT NULL constraint](https://www.mindstick.com/interview/1933/what-is-not-null-constraint) does not allow a column to accept NULL value. In other words, NOT NULL constraint enforces a column to always contain a value. Following is the example of NOT NULL:\

**Example:**

```
CREATE TABLE student_info(
S_Id int NOT NULL,
Name varchar(255) NOT NULL,
Address varchar(255),
City varchar(255)
)
```

In the above example, student id and Name cannot be left blank where as address and city can be left blank.

**UNIQUE CONSTRAINTS**

The [UNIQUE constraint](https://www.mindstick.com/forum/159036/how-can-i-create-a-unique-constraint-that-also-allows-nulls) ensures that each record in a [database table](https://www.mindstick.com/forum/159366/how-to-test-regular-expressions-in-sql-without-using-database-table) must be unique. The UNIQUE and PRIMARY KEY both ensures uniqueness for a column or a set of column. A PRIMARY KEY automatically has a UNIQUE constraint define on it.

The difference between PRIMARY and UNIQUE constraint is that a table can have many UNIQUE constraint, but only one PRIMARY KEY. Following is the example of UNIQUE constraint:

```
CREATE TABLE student_info(S_Id int NOT NULL,Name varchar(255),Address varchar(255),City varchar(255),UNIQUE (S_Id)
)
```

**PRIMARY KEY CONSTRAINTS**

The PRIMARY KEY uniquely identifies record in a table. PRIMARY KEY ensures that these records must be unique in a database table. Each table can only have ONE PRIMARY KEY and that column cannot be left blank or cannot contain NULL values. Following is the example of PRIMARY KEY constraint:

```
CREATE TABLE student_info(S_Id int PRIMARY K EY,Name varchar(255),
Address varchar(255),City varchar(255),)
```

## FOREIGN KEY CONSTRAINTS

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Let’s illustrate with the help of an example:

**Student_info table:**

| \| ## studentId \| ## Name \| ## Address \| \| --- \| --- \| --- \| \| 12ST09 \| John \| USA \| \| 12ST10 \| Royce \| Argentina \| \| 12ST11 \| Mickel \| London \| \| 12ST12 \| Tom \| New york \| \| 12ST13 \| Hugh \| Luanda \| \| 12ST14 \| Nolan \| Paris \| \| 12ST15 \| Jennifer \| USA \| |  |  |
| --- | --- | --- |
| ## studentId | ## Name | ## Address |
| 12ST09 | John | USA |
| 12ST10 | Royce | Argentina |
| 12ST11 | Mickel | London |
| 12ST12 | Tom | New york |
| 12ST13 | Hugh | Luanda |
| 12ST14 | Nolan | Paris |
| 12ST15 | Jennifer | USA |

## Marks table:

| \| ## studentId \| ## Contact Marks \| \| --- \| --- \| \| 12ST09 \| 456788 54 \| \| 12ST10 \| 457912 34 \| \| 12ST11 \| 56276 45 \| \| 12ST12 \| 2898938 43 \| \| 12ST13 \| 2974789 37 \| \| 12ST14 \| 232688 56 \| \| 12ST15 \| 43762762 67 \| |  |
| --- | --- |
| ## studentId | ## Contact Marks |
| 12ST09 | 456788 54 |
| 12ST10 | 457912 34 |
| 12ST11 | 56276 45 |
| 12ST12 | 2898938 43 |
| 12ST13 | 2974789 37 |
| 12ST14 | 232688 56 |
| 12ST15 | 43762762 67 |

In the above example, two tables are given: student_info and marks

Studentid is PRIMARY KEY for student_info table and FOREIGN KEY for marks table.

The **FOREIGN KEY** prevents action that would destroy links between tables and also prevents invalid data from being inserted into the foreign key column.

## CHECK CONSTRAINTS

The [CHECK constraint](https://www.mindstick.com/forum/156741/why-are-use-the-sql-check-constraint-in-the-database-at-table-creation) ensures the limit on value range that can be placed in a column. When CHECK constraint is applied on [single column](https://www.mindstick.com/forum/159327/how-to-set-all-values-in-a-single-column-mysql-query) it allows only certain values for this column. Following is the example of CHECK constraint:

```
CREATE TABLE student(
S_Id int CHECK (S_Id>0),
name varchar(255) NOT NULL,
Address varchar(255),
City varchar(255),
)
```

## DEFAULT CONSTRAINTS

DEFAULT constraints insert the default value into a column. The default value is added to all records, if no other value is specified.\

```
CREATE TABLE student(
S_Id int CHECK (S_Id>0),
name varchar(255) NOT NULL,
Address varchar(255),
City varchar(255) DEFAULT ‘Denmark’,
)
```

**CREATE INDEX**

CREATE INDEX is used to create an index. An index is used to find the data quickly and efficiently.

**Note:** When updating a table with index, it takes more time then without index. So, only index need to be created on fields. Following is the example of CREATE INDEX:

```
CREATE INDEX SIndex
ON student (LastName)
```

## DROP INDEX

Index, table and database can be removed using DROP index. Following is the example of DROP index:

```
DROP INDEX name_of_index  ON  name_of_table
```

## ALTER TABLE

The ALTER statement is used to delete, add, update table. Following is an example of ALTER TABLE:

```
ALTER TABLE name_of_table
DROP COLUMN  name_of_column
```

## [AUTO INCREMENT](https://www.mindstick.com/forum/193/auto-increment-by-store-procedure)

AUTO_INCREMENT generates unique value when a new record is inserted. Following is the example of AUTO INCREMENT:

```
CREATE TABLE Persons(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
)
```

\

---

Original Source: https://www.mindstick.com/blog/11170/sql-constraints

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.