What are Constraints in SQL?
Constraints in SQL are not mandatory to use while creating the table. Still, they are very helpful in maintaining the accuracy, reliability, and integrity of the data during the operations performed on the table in a relational database. In this article, we will briefly discuss different SQL constraints.
Constraints in SQL ensure the accuracy, reliability, and consistency of the data. This article will discuss SQL constraints, their types, and how they are used in the database. We will cover everything from PRIMARY KEY to FOREIGN KEY, NOT NULL to UNIQUE.
So, let’s start the article.
Table of Content
Best-suited Database and SQL courses for you
Learn Database and SQL with these high-rated online courses
What are SQL Constraints?
Constraints in SQL are a set of rules or restrictions defined on columns of a table in a relational database to control the data or data type that can be input or stored in a column. These rules or restrictions ensure the accuracy and reliability of the input data. After the constraint is defined, the particular operation is aborted if any operation in the database violates the specified rule.
In SQL, constraints are broadly divided into two types:
- Column Level Constraints: It refers to a single column in the table and does not specify the column's name except the CHECK constraints.
- Table Level Constraints: It refers to one or more columns in the table and specify the column name in which they apply.
Syntax
In SQL, constraints are applied during the creation of the table using the CREATE TABLE command or after the creation of the table using the ALTER TABLE command.
Syntax-1:
CREATE TABLE table_name(column_name_1 data_type (size) constraint_name,column_name_2 data_type (size) constraint_name,column_name_3 data_type (size) constraint_name,………);
ALTER TABLE table_name(MODIFY column_name_1 data_type (size) constraint_name,MODIFY column_name_2 data_type (size) constraint_name,MODIFY column_name_3 data_type (size) constraint_name,………);
Types of SQL Constraints:
There are mainly six different constraints that are used in SQL:
NOT NULL
- Applied only on the column level
- By default, any column can have a NULL values
- It restricts the columns in a table from having NULL values
- Example:
CREATE TABLE Student( Student_ID INT NOT NULL,Roll_No INT NOT NULL,Name VARCHAR (100));
In the above example, the columns Student_ID and Roll_No of the Student table can’t have a NULL value.
Must Read: What is the difference between SQL and MySQL?
Must Read: Difference between SQL and NoSQL
UNIQUE
- This applies on both table and column level
- It ensures that the column has only unique values
- Example
CREATE TABLE Student( Student_ID INT UNIQUE,Roll_No INT UNIQUE,Name VARCHAR (100));
In the above example, the columns Student_ID and Roll_No of the Student table don’t have any duplicate values.
Note: Primary Keys are always unique, but the constraint columns may or may not be Primary Keys.
DEFAULT
- This applies on both table and column level
- It provides a default value for a column if no value is assigned
Example:
CREATE TABLE Product(Product_ID VARCHAR (50) NOT NULL,Product_Name VARCHAR (50) UNIQUE,Country of Origin VARCHAR (70) DEFAULT ‘India’);
The above table contains three columns: Product ID, Product Name, and Country of Origin. In the third column, if some entries are left out, then the entries are filled with India.
Must Read: Subqueries in SQL
Must Read: SQL CREATE TABLE
CHECK
- This applies on both table and column level
- It is used to restrict the value of a column between a range
- It is similar to data validation in Excel
- Example
CREATE TABLE Student(Name VARCHAR(100) NOT NULL,Age INT(2) CHECK (Age >= 21));
In the above table, we collect the records of all students of age greater than or equal to 21.
Must Read: SQL WHERE
Must Read: Introduction to Normalization
PRIMARY KEY
- This applies on both table and column level
- The primary Key constraint is a combination of both UNIQUE and NOT NULL constraints.
- It helps to retrieve query results from the table.
- Only one PRIMARY KEY can be created per table
- Example:
CREATE TABLE Employee(Employee ID INT PRIMARY KEY,Employee Name VARCHAR (55),Department VARCHAR (55) NOT NULL);
Two employees of the same name may be working in the same department, but every employee must have an employee ID, which should always be unique. So, in the above table, the Employee ID column is a unique identifier for a row.
Must Check: Primary Key in SQL
FOREIGN KEY
- This applies on both table and column level
- It is used to relate two or more tables and prevents the operation that destroys the link between the tables.
- A foreign key can be a primary key if the table is connected by a one-to-one relationship, not a one-to-many relationship.
- Multiple foreign keys can be created per table
- Example:
CREATE TABLE Order(Order ID INT PRIMARY KEY,Product ID INT REFERENCES Products (ID) );
Here, the value of Product ID in the order table must be a value from the ID column of a Product table.
Importance of SQL Constraints
It is not mandatory to define the constraints but
- It maintains the accuracy, reliability, and integrity of the data during the operations performed on the table.
- Helps to enforce the limit on the input so that operations performed after defining the constraint can’t be aborted
Must Read: SQL LIMITS
Must Check: SQL Online Course and Certifications
Conclusion
In this article, we have discussed different SQL constraints with examples.
I hope this article will help you in your Data Science/Data Analysis journey.
FAQs
What are constraints in SQL?
Constraints in SQL are a set of rules or restrictions defined on columns of a table in a relational database to control the data or data type that can be input or stored in a column. These rules or restrictions ensure the accuracy and reliability of the input data. After the constraint is defined, if any operation in the database violates the specified rule, the particular operation is aborted.
What are the different types of constraints in SQL?
Primary Key, Foreign Key, NOT NULL, UNIQUE, CHECK, DEFAULT, INDEX are the different types of constraints in SQL.
How can you add a constraint to a SQL table?
Constraints can be added during the creation of a table using the CREATE TABLE statement or after the table is created using the ALTER TABLE statement.
How do you remove a constraint from a SQL table?
Constraints can be removed from a SQL table using the ALTER TABLE command. However, you need to know the name of the constraint.
What is a Primary Key Constraint?
A Primary Key constraint ensures that each row in a table has a unique identifier. It cannot contain NULL values and is used to uniquely identify each record.
How does a Foreign Key Constraint work?
A Foreign Key constraint is used to create a link between the columns in two tables. It ensures referential integrity by referencing a primary or unique key in another table.
What is the purpose of a Check Constraint?
A Check constraint is used to define a specific condition that each row must satisfy. For instance, it can ensure that a column only contains values for adults (e.g., CHECK (Age>=18)).
How does the Default Constraint function?
The Default constraint sets a default value for a column when no value is specified. If a record is inserted without a value for this column, the default value is automatically assigned.
Can I use a Unique Constraint on multiple columns?
Yes, you can apply the Unique constraint to multiple columns. This ensures that the combination of values in these columns is unique across all rows in the table.
What happens if a constraint is violated during an INSERT or UPDATE operation?
If a constraint is violated, the SQL operation (INSERT or UPDATE) will fail, and the database will return an error message. This ensures data integrity and adherence to the defined rules.
Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio
Top Picks & New Arrivals
