Blog Blog Posts Business Management Process Analysis

SQL Constraints – Everything You Should Know

Understanding SQL constraints is crucial for database developers and administrators who want to build robust, error-free databases. SQL Constraints are statements used at the column or table level to define rules for inserting data into a column or table. Constraints are restrictions on data types that ensure data’s reliability, consistency, and accuracy. Learn more from this blog, and explore some practical examples of using various SQL constraints effectively in your database.

What are the Constraints in SQL?
How to Create Constraints in SQL?
Types of Constraints in SQL
Conclusion

Watch this Video on MS SQL Tutorial for Beginners

{
“@context”: “https://schema.org”,
“@type”: “VideoObject”,
“name”: “video”,
“description”: “SQL Constraints – Everything You Should Know”,
“thumbnailUrl”: “https://img.youtube.com/vi/LGTbdjoEBVM/hqdefault.jpg”,
“uploadDate”: “2023-06-26T08:00:00+08:00”,
“publisher”: {
“@type”: “Organization”,
“name”: “Intellipaat Software Solutions Pvt Ltd”,
“logo”: {
“@type”: “ImageObject”,
“url”: “https://intellipaat.com/blog/wp-content/themes/intellipaat-blog-new/images/logo.png”,
“width”: 124,
“height”: 43
}
},
“embedUrl”: “https://www.youtube.com/embed/LGTbdjoEBVM”
}

What are the Constraints in SQL?

Constraints in SQL are rules you can set to control the data inserted or updated in a table. These specific rules ensure that the data in your tables is accurate and consistent, preventing errors and inconsistencies. Constraints set to enforce a range of regulations, from providing each record with a unique identifier to certain columns with a specific data type.

How to Create Constraints in SQL?

In SQL, you can create constraints using the CREATE TABLE command when creating a new table or using the ALTER TABLE command to modify an existing table. Constraints are rules that define data integrity and enforce specific conditions on the columns of a table.

To create constraints using the CREATE TABLE command, you can use the following syntax:

SQL:

CREATE TABLE table_name (
    column_name1 data_type(size) constraint_name,
    column_name2 data_type(size) constraint_name,
    ...
);

In this syntax:

‘table_name’ is the name of the table you want to create.
‘column_name’ is the name of the column you want to create.
‘data_type’ is the data type of the column.
‘size’ specifies the maximum size or length of the column.
‘constraint_name’ is the name you want to give to the constraint.

Alternatively, if you want to create constraints using the ALTER TABLE command, use the ADD CONSTRAINT clause.

Here’s the syntax:

SQL:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
column_name data_type(size);

In this syntax:

‘table_name’ is the name of the table to which you want to add the constraint.
‘constraint_name’ is the name you want to give to the constraint.
‘column_name’ is the column's name to which the constraint applies.
‘data_type’ represents the data type of the column.‘size’ specifies the maximum size or length of the column.

Types of Constraints in SQL

There are various types of SQL constraints to aim for the accuracy and consistency of data in a table. Here are some common types of constraints, along with the SQL constraints are explained with examples:

NOT NULL

This constraint ensures that a column in a table must have a value and cannot be left blank or empty. It is useful for columns that must always contain data, such as a customer’s name or an order number.

NOT NULL Constraints on CREATE TABLE
Below is an example of NOT NULL MySQL constraints “UserID,” “FirstName,” and “LastName” columns will NOT accept NULL values when the “Mytable” table is created.

Example

CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255) NOT NULL,
JobPosition varchar(255),
);

NOT NULL Constraints on ALTER TABLE
Below is an example to create a NOT NULL ALTER TABLE MySQL constraints on the “JobPosition” column when the “Mytable” table is created.

Example

ALTER TABLE Mytable
MODIFY JobPosition varchar(255) NOT NULL;

Get familiar with the top SQL Interview Questions to get a head start in your career!

UNIQUE

Unique constraint ensures that the data in a column is unique across all records in the table.

UNIQUE Constraints on CREATE TABLE
Below is an example of creating a UNIQUE constraint on the “UserID” column in the table “Mytable.”

Example

CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
JobPosition varchar(255),
UNIQUE (UserID)
);

Use the following SQL syntax to name a UNIQUE constraint and define a UNIQUE constraint on several columns.

CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
JobPosition varchar(255),
CONSTRAINT UC_Mytab UNIQUE (UserID, JobPosition)
);

UNIQUE Constraints on ALTER TABLE
Below is an example to create a UNIQUE constraint on the “UserID” column in the existing table.

Example

ALTER TABLE Mytable
ADD UNIQUE (UserID);

Use the following SQL syntax to name a UNIQUE constraint and define a UNIQUE constraint on several columns.

ALTER TABLE Mytable
ADD CONSTRAINT UC_Mytab UNIQUE (UserID, JobPosition);

UNIQUE Constraints on DROP TABLE
Below is an example to DROP a UNIQUE Constraint.

ALTER TABLE Mytable
DROP INDEX UC_Mytab;

PRIMARY KEY

This type of SQL constraint is developed to aim that each record in the table has a very unique identifier. It is typically applied to a column that contains a unique value for each record, such as an ID number or a name.

PRIMARY KEY ON CREATE A TABLE
Below is an example of creating a PRIMARY KEY on the “UserID” column in a “Mytable” table.

Example

CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
JobPosition varchar(255),
    PRIMARY KEY (ID)
);

Use the following SQL syntax to enable the naming of a PRIMARY KEY constraint and to define a PRIMARY KEY constraint on multiple columns.

CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
JobPosition varchar(255),
    CONSTRAINT PK_Mytab PRIMARY KEY (UserID, JobPosition)
);

PRIMARY KEY ON ALTER TABLE
Below is an example to create a PRIMARY KEY on the “UserID” column in the existing table.

Example

ALTER TABLE Mytable
ADD PRIMARY KEY (ID);

Use the following SQL syntax to name a PRIMARY KEY constraint and define a PRIMARY KEY constraint on multiple columns.

ALTER TABLE Mytable
ADD CONSTRAINT PK_Mytab PRIMARY KEY (UserID, JobPosition);

Career Transition

PRIMARY KEY ON DROP TABLE
Below is an example to DROP a PRIMARY KEY constraint.

ALTER TABLE Mytable
DROP PRIMARY KEY;

Want to learn more about SQL? Here is the Online Microsoft SQL training provided by Intellipaat.

FOREIGN KEY

Foreign Key constraint establishes a relationship between two tables, typically by linking a column in one table to a primary key in another table. It ensures that the data in the linked columns is consistent and accurate across both tables.

FOREIGN KEY ON CREATE TABLE
Below is an example of a Foreign Key Constraint.

There are two tables named “Customer” & “Orders.”

Example

Customer Table

CREATE TABLE Customer (
CustomerID int NOT NULL PRIMARY KEY, 
Name varchar(45) NOT NULL,
Age int,
City varchar(25)
);

Orders Table

CREATE TABLE Orders (  
Order_ID int NOT NULL PRIMARY KEY,  
Order_Num int NOT NULL,  
CustomerID int,  
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)  
);

Here, you can see that the “CustomerID” field in the “Orders” table points to the “CustomerID” field in the “Customer” table. The “CustomerID” is the PRIMARY KEY in the “Customer” table, while the “CustomerID” column of the “Orders” table is a FOREIGN KEY.

CHECK

This constraint ensures that the data in a column meets a specific condition or set of conditions.

CHECK Constraints on CREATE TABLE
Below is an example of CHECK constraints on the “VoterAge” column in a table named “Mytable” which will ensure that a user’s age must be 18 or more.

Example

CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
VoterAge int,
    CHECK (VoterAge>=18)
);

CHECK Constraints on ALTER TABLE
Below is an example of creating CHECK constraints on the “VoterAge” column in an existing table.

Example

ALTER TABLE Mytable
ADD CHECK (VoterAge>=18);

CHECK Constraints on DROP TABLE
Below is an example to DROP a CHECK constraint from the table.

Example

ALTER TABLE Mytable
DROP CHECK CHK_VoterAge;

DEFAULT

A default constraint is used to specify a default value for a column in a table. The default value will be used instead if no value is selected for the column when a new row is inserted.

DEFAULT Constraints on CREATE TABLE
Below is an example that sets a DEFAULT value in the column “JobPosition” in a table named “Mytable.”

Courses you may like

Example

CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
JobPosition varchar(255) DEFAULT 'Technical'
);

DEFAULT Constraints on ALTER TABLE
Below is an example to set a DEFAULT value in the column “JobPosition” in an existing table.

Example

ALTER TABLE Mytable
ALTER JobPosition SET DEFAULT 'Technical’;

DEFAULT Constraints on DROP TABLE
Below is an example to DROP a DEFAULT Constraint from the table.

ALTER TABLE Mytable
ALTER JobPosition DROP DEFAULT;

AUTO_INCREMENT

An auto-increment constraint automatically generates a unique value for a column each time a new row is inserted into a table. It is specifically used for primary key columns so that each new row is automatically assigned a unique identifier.

Below is an example where the UserID column will be auto-incremented in the “Mytable.”

Example

mysql> CREATE TABLE Mytable(  
UserID int NOT NULL AUTO_INCREMENT,  
name CHAR(30) NOT NULL,   
PRIMARY KEY (UserID)
);

Next, we need to insert the values into the table “Mytable.”

mysql> INSERT INTO Mytable (name) VALUES   
('Anshu'),('Shiv'),('Naveen'),   
('Nil'),('Kuldeep'),('Ravindra');.

And automatically, MySQL will generate a series of numerical numbers.

Conclusion

SQL constraints are essential to database design and management. They ensure the integrity and accuracy of data by enforcing rules that restrict specific actions on the database. These little rules and regulations may seem insignificant, but they play a crucial role in ensuring the accuracy and reliability of your data.

Understanding how to use constraints effectively when designing and managing a database is essential. With careful planning and implementation, constraints can help ensure the accuracy and reliability of data in any database system.

Visit our SQL Community to get answers to all your queries!

The post SQL Constraints – Everything You Should Know appeared first on Intellipaat Blog.