What are SQL Constraints and their Different Types?

What are SQL Constraints and their Different Types?
What are SQL Constraints and their Different Types?

Introduction

If you’re learning about database management, then you must study SQL. It offers many functionalities that allow you to manage data in a relational database management system effectively.

One of those functionalities is the ability to use constraints. You might wonder, “What are these constraints in SQL?” In the following article, we’ll help you find the answer to this question as we’ll explore the various constraints in SQL and understand their uses. 

You can also take a deep dive into SQL and data management with our advanced coding course on Data Science and Machine Learning

What are Constraints in SQL? 

As the name suggests, constraints are rules that you enforce on the data columns of a table. You use them to limit the type of data that goes into a table. 

By using constraints, you can ensure that the data remains reliable and accurate. Constraints in SQL are either column level or table level. Column level constraints enforce rules on a particular column while a table-level constraint limits the entire table. 

SQL mainly has the following six constraints:

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

Let’s learn about each of them in detail: 

NOT NULL 

By default, columns can hold NULL values in SQL. If you don’t want a column to have a NULL value, you can use the NOT NULL constraint. 

The NOT NULL prevents the column from having a NULL value as it specifies that a NULL value is not allowed. However, keep in mind that a NULL value isn’t the same as no data. It represents unknown data. 

blog banner 1

Example: In the following example, we have a new table called CLIENTS with five columns. In three of those columns, we have specified that we don’t accept NULL values:

CREATE TABLE CLIENTS (

ID INT 			NOT NULL,
NAME VARCHAR 	NOT NULL,
AGE INT		NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (17,1),
PRIMARY KEY (ID)

);

If you have already created the table, you can still add a NOT NULL constraint to the SALARY column by using the following query:

ALTER TABLE CLIENTS
	MODIFY SALARY (17,1) NOT NULL;

UNIQUE

If you don’t want two records to have the same (identical) values in a column, you would use the UNIQUE constraint. It is certainly one of the most important constraints in SQL and you’ll be using it frequently. 

Example: In the following table, we have set the AGE column as unique so two clients can’t have the same age. Keep in mind that it’s an elementary example. 

You might use the UNIQUE constraint to prevent two items to have the same ID or the same passcode:

CREATE TABLE CLIENTS (

ID INT 			NOT NULL,
NAME VARCHAR 	NOT NULL,
AGE INT		NOT NULL UNIQUE,
ADDRESS CHAR (25) ,
SALARY DECIMAL (17,1),
PRIMARY KEY (ID)

);

PRIMARY KEY

A primary key is a field in a table that identifies every row in a database table uniquely. It contains unique values and so, can’t have NULL values.

One table can only have one primary key, which could have single or multiple fields. If you use multiple fields as a primary key, it’s called a composite key. If a table has a primary key defined on a field, you can’t have two records having the same value of that field. 

Example: Below, we have a table titled CLIENTS with five columns. Here, we have defined the ID attribute as a primary key:

CREATE TABLE CLIENTS (

ID INT 			NOT NULL,
NAME VARCHAR 	NOT NULL,
AGE INT		NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (17,1),
PRIMARY KEY (ID)

);

If you have already created the table, you can add a primary key by using the following command:

ALTER TABLE CLIENTS ADD PRIMARY KEY (ID) ;

FOREIGN KEY

You use a foreign key to link two tables. Another name for Foreign Key is Referencing Key. It is a column or combination of such columns, the values of which match a primary key present in a different table. 

If a primary key is in a table and it is specified on a field, there shouldn’t be two records of the same value of that specific field. 

Example: Here we have two tables:

CREATE TABLE CLIENTS (

ID	INT 			NOT NULL,
NAME 	VARCHAR 	NOT NULL,
AGE 	INT		NOT NULL,
ADDRESS 	CHAR (25) ,
SALARY DECIMAL (17,1),
PRIMARY KEY (ID)

);

CREATE TABLE ORDERS (

ID			INT		NOT NULL,
DATE			DATETIME,
CUSTOMER_ID	INT references CLIENTS (ID),
AMOUNT		double,
PRIMARY KEY	(ID)

);

Here, you can see that the CUSTOMER_ID is the Foreign Key for both of the tables. 

CHECK

The CHECK constraint adds a condition of checking the value being entered into a record. If the condition isn’t met, the record violates the constraint and doesn’t enter the table. 

Example: Here, we have a table titled CLIENTS with five columns. We have added a CHECK constraint with the AGE column so we can’t have any client below the age of 18 years:

CREATE TABLE CLIENTS (

ID INT 			NOT NULL,
NAME VARCHAR 	NOT NULL,
AGE INT		NOT NULL CHECK (AGE >= 18),
ADDRESS CHAR (25) ,
SALARY DECIMAL (17,1),
PRIMARY KEY (ID)

);

DEFAULT 

The DEFAULT constraint adds a default value to a column when the user hasn’t provided a specific value. 

Example: In the following example, we have created a new table called CLIENTS with five columns. In the SALARY column we have provided a default value of 10000.00 so if the INSERT INTO statement doesn’t provide a value for this column, its default value would be 10000.00. 

CREATE TABLE CLIENTS (

ID INT 			NOT NULL,
NAME VARCHAR 	NOT NULL,
AGE INT		NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (17,1) DEFAULT 10000.00,
PRIMARY KEY (ID)

);

Frequently Asked Questions

What are constraints in SQL?

Constraints refer to the rules we can apply to any data type in a table. We can specify the limit on the data type that can be stored in a specific column in a table by using constraints in SQL.
In other words, they are rules that limit the type of data that can go into a table. They help maintain the integrity and accuracy of the data inside a table.

How many constraints are there in SQL?

There are six primary constraints in SQL. These are:

1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK
6. DEFAULT
7. CREATE INDEX

While using SQL, you’ll be using these six constraints the most, hence you should learn extensively about them.
You can start with our free program on Data Science and Machine Learning Complete to learn SQL and its related concepts. It would help you build a strong foundation for your learning journey.

What are the types of constraints?

There are two types of constraints in SQL:

1. Column level constraints
2. Table level constraints

Column level constraints limit only column data in SQL while table level constraints limit whole table data.

What are the four types of constraints?

This question relates to constraints on Relational database models, rather than the SQL constraints.
The four primary constraints in a relational database are:

1. Referential integrity constraints
2. Entity integrity constraints
3. Key constraints
4. Domain constraints

Many people confuse the constraints of relational database models with the constraints in SQL. Keep in mind that both of them are different subjects and hence, have different constraints.

What are three major types of constraints?

This is another question that many people get confused about. It is about the theory of organisations and it says that you can measure an organisation and control it by variations on three measures: throughput, operational expense, and inventory.

SQL has two types of constraints only, namely, column level constraints and table level constraints.
Column level constraints only one column’s data while table level constraints limit the entire table’s data.

What is an example of a constraint?

A constraint is something that imposes a restriction or limit that prevents something from happening.
A popular example of a constraint is the limited number of hours in a day. You can only accomplish so much in a single day because it only has 24 hours.

What does the check constraint do in SQL?

The CHECK constraint helps validate the values of a column to meet a certain condition. What does it mean? It means this constraint ensures that the value stored in a specific column meets a specific condition.

How do I find a unique constraint in SQL?

To see if a column has unique constraint, you should use the following method:

select count(*) cnt from
USER_IND_COLUMNS cols
where cols.table_name=’YOUR_TABLE_NAME’
and cols.COLUMN_NAME=’YOUR_COLUMN’ ;

You can also look in the ALL_IND_COLUMNS and ALL_CONSTRAINTS views to check for a unique constraint in SQL.

What is the default constraint in SQL?

The DEFAULT constraint specifies a default value for the column when the user hasn’t specified any value for the same.

Key Takeaways

SQL has six main constraints:

  • NOT NULL
  • DEFAULT
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK CONSTRAINT

You can use constraints in SQL to add limitations to your columns and tables. This makes it easier to maintain the data type’s integrity and keeps them more manageable. If you are learning SQL, it is important to wrap your heads around the constraints so that you can navigate through them properly.

To read more articles and explore Coding Ninjas programming courses, you can visit our website.