Constraints in SQL: A Complete Guide with Examples

Introduction

The principles that we can apply to the kinds of data in a table are called constraints. In other words, we may use Constraints in SQL to set a limit on the kind of data that can be kept in a specific table field. To preserve the integrity, precision, and dependability of the data in a given column, SQL Server constraints are established guidelines and limitations that are applied to one or more columns. In simpler terms, the inserted value will be successfully entered if it satisfies the constraint rule. The insert operation will fail if the entered data deviates from the specified constraint. We will explore more about the types of SQL constraint example:

What are the constraints in SQL?

To specify the rules for the data in a table Constraints in SQL are used. The quality of the data that will go into the table is controlled with the help of SQL constraints. This will make sure the quality and reliability of the data is good. The action will be aborted, if there is any error in the data and constraint. There are several constraints like column level or table level. Column level constraint in SQL server applies to column and table level constraint to the whole table.

Importance of SQL constraints

Constraints in SQL are important because they ensure the accuracy and reliability of data in a table by limiting the type of data that can be entered. Constraints can be at the column or table level and prevent data from being duplicate, NULL, or mismatched. They also help to enforce business logic, relational integrity, and uniqueness.

1. SQL constraints are used to maintain the integrity of the data during updation/deletion/insertion in a table.
2. It makes sure that all the operations in a database must follow the specified rules, if any operation fails to do so, then the operation will be aborted or rolled back.
3. A constraint is a property that is applied to a column or group of columns in a database table that stops inconsistent data values from being entered into those columns.
4. The integrity of the data is protected by constraints. This upholds the accuracy and dependability of the data kept in the database.
5. A few integrity Constraints in SQL are included in the relational database model to ensure data consistency and accuracy.
6. Certain requirements for data quality are defined by data integrity and must be fulfilled by the data in the database.
   

Implementing Constraints in SQL Queries

You can create Constraints in SQL while creating a table with the help of using the CREATE TABLE command or ALTER TABLE command. If we use the ALTER TABLE command, the operation will only succeed if it follows the specified rules.

The basic syntax for creating a constraint is:

CREATE TABLE table_name(

Column_name1 data_type(size) constraint_name,

Column_name2 data_type(size) constraint_name,

….

);

In the above table:

• Table_name: name of the that we want to create
• Column_name: name of the column we want to choose
• Data_type: datatype of the value needed to be entered into the column
• Size: length of the column
• Constraint_name: name of the constraint that needs to be created.
  

How do you name a constraint?

We can name the constraints whenever we define them. We can rapidly link a specific constraint to our data model if all of the constraints have accurate names. This offers us two significant benefits:

1. We can locate and correct problems fast.

2. We can consistently add, remove, or alter constraints.

A constraint naming standard could have the following basic format:

(table name)_(column_name)_(constraint abbreviation)

Different types of constraints in SQL?

SQL constraints refer to the limitations or conditions we impose on the database. This also implies that we verify certain conditions before adding data to the database. Only the data that needs to be put into the database tables will be added if the criterion we set for the database remains true. There are seven types of constraints in SQL.

1. NOT NULL Constraint in SQL: A NOT NULL constraint in SQL is used to make sure that a column should not accept a null value entered by the user, it makes sure that a new value is inserted or the records are not updated with the null value. The NOT NULL constraint can be specified when creating a table or added later. For example:

CREATE TABLE Persons (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255) NOT NULL,

Age int

);

2. UNIQUE Constraint in SQL: A SQL unique constraint is used to make sure that all the values in a table are different or unique, no two values should be same. A unique constraint ensures the uniqueness of every column in a table by not letting any two similar values be entered by the user. It returns a boolean value that indicates the presence or absence of duplicate tuples. The constraint returns true if the subquery has no duplicate tuples, and false if it does.

CREATE TABLE table_name ( column_name data_type UNIQUE );

3. PRIMARY KEY Constraint: just like the UNIQUE constraint, the SQL constraint primary key also ensures that all the records are unique and different from each other, and are not null. The function of both unique and primary key constraints is to maintain the uniqueness of the data or the records present or being entered in the table. A primary key constraint is a type of constraint that specifies that a table's columns must uniquely identify each row. It combines a NOT NULL constraint and a unique constraint in a single declaration. A primary key constraint prohibits multiple rows from having the same value in the same column or combination of columns, and prohibits values from being null. For example:

CREATE TABLE Colleges (

college_id INT,

college_code VARCHAR(20) NOT NULL,

college_name VARCHAR(50),

CONSTRAINT CollegePK PRIMARY KEY (college_id))

);

4. FOREIGN KEY Constraint: A foreign key constraint SQL is a combination of columns that are connected, it checks whether the data stored in a column of some another foreign table is similar to the data stored in the column of its table. Foreign keys establish relationships between tables and promote consistency. Foreign key constraint in SQL enforces referential integrity, which means that if column value A refers to column value B, then column value B must exist. For example:

Table People:

CREATE TABLE People(

PeopleID int PRIMARY KEY,

FirstName varchar(155) NOT NULL,

LastName varchar(155) NOT NULL

);

Table Orders:

CREATE TABLE Orders (

OrderID int PRIMARY KEY,

People IDint,

OrderDate date,

FOREIGN KEY (PeopleID) REFERENCES People(PeopleID)

);

INSERT INTO People (PeopleID, FirstName, LastName)

VALUES (1, ‘Joe, 'Ann'),

(2, 'Janice', 'Smith');

INSERT INTO Orders (OrderID, PeopleID, OrderDate)

VALUES (1, 1, '2023-04-01'),

(2, 2, '2024-02-01');

5. CHECK Constraint: check constraint in SQL is used to set a range for the data that will be entered into the column, it restricts the data out of the specified range to be entered into the column. Check constraint in DBMS will only allow the specific values to be entered which are in the range specified by the used to enter.

For example:

Check_last_name

6. DEFAULT Constraint: The default constraint in SQL is used to enter the default values and fix a value in a column that will not change. If we will not provide any other value then the same default value would be entered into the records. For example:

CREATE TABLE User(

User_Id int NOT NULL UNIQUE,

User_Amount int NOT NULL,

User_Name varchar(255),

User_Date date,

Profit int,

CHECK (User_Name<> ’ABC’)

);

7. CREATE INDEX Constraint: CREATE INDEX constraint is used to create an index in a database. An index can be created in a database by using a single or a group of columns in a table.

CREATE INDEX idx_id

ON USer(User_Id);

Conclusion

The design and management of databases depend on Constraints in SQL. By imposing rules that limit particular actions in the database, they guarantee the accuracy and integrity of the data. Although these little laws and guidelines may seem like little, they are important for guaranteeing the dependability and correctness of your data. We have also covered about primary key and foreign key in SQL with examples. It is essential to comprehend the appropriate utilization of limitations in database design and management. Constraints can contribute to ensuring the correctness and dependability of data in any database system with proper design and execution.

Frequently Asked Questions (FAQs)

1. What are the constraints in SQL?

SQL constraints are used for specifying the rules for the data in the table. The quality of the data entered in the table limits the data type of the data being entered.

2. How many constraints are there in the SQL table?

There are mainly six main constraints in the SQL table, these constraints are:

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

3. Is the primary key a constraint?

Yes, a primary key is a constraint that is used uniquely to identify every record in the table.

And could be used to specify the relationship between the different tables.

4. Can we give 2 constraints in SQL?

Yes, we can give more than two constraints in SQL, we can easily give more than one constraint to the column of a table that we want to create.

5. What are the 5 constraints in SQL?

There are mainly six main constraints in the SQL table, these constraints are:

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

6. What is not null in SQL?

The NOT NULL constraint in SQL is used to make sure that the column of the table should not accept the null values, which means that you cannot insert or update a record without adding any value.

7. What is constraint in DBMS?

The constraints in a database management system(DBMS) are the rules and conditions that are used to ensure the data integrity and consistency of the data entered into the database.

8. Can a foreign key be NULL?

Yes, the foreign key can be NULL. A foreign key is a column that connects two tables by using the primary key to reference another table in the same database. A foreign key can contain null values, but it cannot match the values of a parent key because a parent key cannot have null values.