SQL Constraints

SQL Constraints: When we have large quantities of data, we must ensure the accuracy and dependability of information stored within databases. To accomplish this, SQL constraints can be used to ensure consistency. In this article, I will discuss various SQL Constraints and provide examples.

Example: To ensure that all bank customers are identified uniquely, we must place a constraint while working with their account data to maintain accuracy and dependability.

What are SQL Constraints?

SQL Constraints can help ensure your database is accurate and reliable by setting rules for the data in a table. By using these constraints, you can guarantee that only valid types of information are stored within the system. SQL Constraints guarantee that any transaction involving data will not result in violations; if any violations occur, the actions will be terminated automatically.

There are two types of constraints are there, that is:

• Column Level SQL Constraints – These constraints are applied to a single column.
• Table Level SQL Constraints – These constraints apply to the complete table.

To better understand, let us look at the different types of constraints. Additionally, I will use this table as an aid while explaining the concept.

SQL Constraints	Description
ADD	Adding Constraint to an existing attribute or Tabel.
NOT NULL	Values cannot be null
UNIQUE	Values cannot match with other value
PRIMARY KEY	Used to identify a row uniquely
FOREIGN KEY	References a row in another table
CHECK	Validates condition for new value
DEFAULT	Set default value if not passed
DROP	To remove an existing constraint
CREATE INDEX	Used to speed up the reading process

Let’s take each of the above constraints individually with examples for a better understanding.

ADD Constraint

You can add a SQL Constraints ADD to an existing table in your database using the ADD CONSTRAINT Statement. This will ensure that your data is secure, consistent, and up-to-date.

You can apply constraints to the ALTER TABLE statement followed by ADD CONSTRAINT command. Here are some of the most commonly used syntaxes for adding constraints on existing tables:

Syntax For Primary Key Add Constraint

ALTER TABLE table_name
ADD CONSTRAINT primarykey_name
PRIMARY KEY (existing_column_name);

Example:

ALTER TABLE employee_details
ADD CONSTRAINT pk_emp_name
PRIMARY KEY (emp_name);  

If you want to know more about the PRIMARY KEY, follow the link.

Syntax For Foreign Key Add Constraint

ALTER TABLE childtable
ADD FOREIGN KEY (childtable_column_name)
REFERENCES parent table (parenttable_column_name);

Example Of Adding Foreign Key Constraint:

CREATE TABLE department_details ( 
   dept_id INT NOT NULL,
   dept_name VARCHAR (20),
   dept_location VARCHAR (20)
   );
    
ALTER TABLE department_details
ADD CONSTRAINT fk_key
FOREIGN KEY (dept_id)
REFERENCES employee_details (dept_id); 

We have discussed Foreign Key In SQL in detail, and You can follow the link for more details.

Syntax For Composite Key Constraint

ALTER table table_name
ADD CONSTRAINT Compkey_name
PRIMARY KEY (required_column_name1, required_column_name2);

Example Of Adding Composite Key constraint:

ALTER TABLE employee_details
ADD CONSTRAINT ck_employee
PRIMARY KEY (emp_id, dept_id);

Syntax For Unique Key Constraints

ALTER TABLE table_name
ADD UNIQUE (column_name); 

And here is an example of a Unique key constraint

ALTER TABLE employee_details
ADD CONSTRAINT uq_emp_name 
UNIQUE KEY  (emp_name); 

Syntax For Check Constraint

ALTER TABLE table_name
ADD CONSTRAINT chk_name
CHECK (condition);

Here is an example:

ALTER TABLE employee_details  
ADD CONSTRAINT chk_ dept_id
CHECK  (dept_id = 1000  OR dept_id = 2000);

SQL NOT NULL Constraint

A NOT NULL constraint ensures that no cell value for any row in this column can be empty. This rule is typically used when the data in a table column is essential to identify and extract information.

We can add the NOT NULL constraint to a table while creating a table or add it to an existing table using the Alter statement.

Example:

Let’s create a Sales table where the Sale_Id has the NOT NULL constraint.

CREATE TABLE Sales (
    Sale_Id int NOT NULL,
    Sale_Amount int,
    Vendor_Name varchar(255),
    Sale_Date date,
    Profit int
);

The above table shows that the Sale_Id column is assigned with NOT NULL.

ADD NOT NULL Constraint into an existing Table: We will try to add a NOT NULL constraint into the Sale_Amount and Vendor_Name columns. To do that, we can take the help of the SQL ALTER Statement.

ALTER TABLE Sales
ALTER COLUMN Sale_Amount INT NOT NULL;

DELETE NOT NULL Constraint From a Table: You can delete the SQL NOT NULL constraint from a table by following the below example:

ALTER TABLE Sales
ALTER COLUMN Sale_Amount INT;

Finally, Here are some of the key points of the SQL NOT NULL Constraint:

• Not Null Constraint allows duplicate values.
• Not Null Constraint does not allow null values.
• Users can create multiple columns with Not Null Constraint.
• Users can add constraints like UNIQUE, PRIMARY KEY, to Not Null Constraint.
• By using Not Null Constraint with PRIMARY KEY user can identify rows uniquely.

Unique Constraint

A UNIQUE constraint is a restriction put on a table column, guaranteeing that no two rows will contain identical values in that specified column. It allows for nulls to be present as this denotes an absence of value and, therefore, cannot equal another row with a null value. Generally, it uniquely identifies each tuple (row) within the table.

We can create UNIQUE Constraints in two ways, that is:

• Using CREATE TABLE Statement.
• Using ALTER TABLE Statement.

Using CREATE TABLE Statement

First, we will see how to add the UNIQUE Constraints while creating a table, and for that, the syntax is:

CREATE TABLE table_name 
( 
   column1 datatype UNIQUE,
   column2 datatype,
      … 
      …                        
   columnN datatype); 

Example:

CREATE TABLE Employee
 (emp_id INT NOT NULL UNIQUE,
  emp_name VARCHAR (20), 
  dept_id  INT);

Executing the above script will create the Employee table where the emp_id attribute has the NOT NULL & Unique constraint.

And the syntax for adding the Unique Constraints in multiple columns is below:

CREATE TABLE table_name 
( 
     column1 datatype,
     column2 datatype,
     [CONSTRAINT uq_name] 
     UNIQUE (required_column1, required_column2, ….., .. )); 

Now we will try to add the Unique constraint to the other two columns, emp_id & dept_id. Let us see the below example:

CREATE TABLE Employee22 ( 
emp_id INT NOT NULL,
emp_name VARCHAR (20),
dept_id  INT, 
CONSTRAINT uq_employee
UNIQUE (emp_name,dept_id));

Using ALTER TABLE Statement

Above, we have shared how to create a table with unique constraints. But now we will discuss adding the unique constraint with an existing table. And for that, you can check the below syntax:

ALTER TABLE employee_details
ADD CONSTRAINT uq_emp_name
UNIQUE (emp_name);

Now we will try to understand how to add unique constraints to multiple columns using Alter Statement.

ALTER TABLE Employee12
ADD CONSTRAINT uq_con_employee
UNIQUE (emp_id, emp_name);

Similarly, if a table has any constraint and you want to remove that, then you can use the DROP statement. Here is the syntax:

ALTER TABLE table_name
DROP CONSTRAINT uq_name;

Above examples, we have added a unique constraint name, uq_con_employee, into the table. Now we will try to remove that unique constraint from the existing table. For that, you can follow the below example:

ALTER TABLE Employee
DROP CONSTRAINT uq_con_employee;

Primary Key Constraint

A primary key constraint is a rule in a relational database that ensures that a specific column or combination of columns uniquely identifies each row of data in a table. This constraint ensures that there are no duplicate values in the specified column(s) and that every row in the table has a unique identifier.

A primary key can be made up of one or more columns, but it must meet the following criteria:

• Unique: Each value in the primary key column(s) must be unique, meaning no two rows can have the same value.
• Not Null: The value(s) in the primary key column(s) cannot be null or blank.
• Unchanging: The value(s) in the primary key column(s) cannot be changed or updated once they have been assigned.

We have discussed in detail the primary key in a separate blog post. You can refer to that post to better understand one of the important concepts of SQL SERVER.

FOREIGN KEY Constraint

A foreign key constraint is a database constraint that ensures the consistency and integrity of data between two related tables. It defines a relationship between a column or set of columns in one table (the child table) and a primary key column or set of columns in another table (the parent table).

The foreign key constraint ensures that data entered into the child table is consistent with the data in the parent table. It also helps to enforce referential integrity, which means that data in the child table cannot reference data that does not exist in the parent table.

For example, in a customer order database, the “customer_id” column in the “orders” table might be a foreign key that references the “id” column in the “customers” table. This would ensure that only valid customer IDs could be entered into the “orders” table and that the data in the “orders” table would always be consistent with the data in the “customers” table.

In most database management systems, a foreign key is implemented using an index, which makes it easy to search for and retrieve specific records quickly. Foreign keys can also create cascading actions, such as automatically deleting child records when a parent record is deleted.

If you want to know more about Foreign Key uses and how to implement them in the SQL Database table, follow the link where we have discussed them in detail with examples.

Check Constraint In SQL

A CHECK constraint in a database management system ensures that data entered into a table adheres to specific rules or conditions. The purpose of a CHECK constraint is to limit the values that can be inserted or updated in a column based on a specific condition or set of conditions.

For example, a CHECK constraint could ensure that a date column only contains dates that fall within a specific range or that a numerical column only contains values greater than or equal to zero.

The syntax for creating a CHECK constraint varies depending on the database management system. Still, it typically involves defining the condition or set of conditions that must be met, along with the name of the column or columns to which the constraint applies.

Here is the syntax For Check Constraints. If you are creating a new table

CREATE TABLE 
( 
     column1 datatype [CHECK (condition)],
     column2 datatype [CHECK (condition)],
     …,    
     columnN datatype [CHECK (condition)],
 [CONSTRAINT constraint_name CHECK (condition)]); 

The above is an example of a simple check containing one condition. But we can find a few examples of conditions that may be put in CHECK constraints, which are:

• Comparison operators (e.g., greater than, less than, equal to)
• Boolean operators (e.g., AND, OR, NOT)
• Regular expressions
• User-defined functions

And Here is the syntax for multiple conditions:

CREATE TABLE ( 
     column1 datatype [CHECK (condition)],
     column2 datatype [CHECK (condition)],
        …,    
     columnN datatype [CHECK (condition)],
  [CONSTRAINT constraint_name CHECK (Condition1 [AND|OR] Condition2)]);

We have seen the syntaxes of the Check constraint with single and multiple conditions. But until we have not seen some real-time examples, it isn’t easy to relate the syntaxes with real-time use. So now let’s check some of the real-time examples:

CREATE TABLE VOTERS(
   SNo   int,
   Name  varchar(266),
   Age   int CHECK (Age >= 18),
   City  varchar(266)
);

This query will create the Voters table, which only includes records for individuals who are eligible to vote (ages 18 and above). If we enter an invalid record such as age which does not meet the CHECK constraint, it may result in a thrown error, and this value will not be entered into the table.

INSERT INTO Voters values(1,'Varma',16,'Delhi');

When we try to insert the above records, it throws the below error:

The INSERT statement conflicted with the CHECK constraint "CK__VOTERS__Age__5070F446". The conflict occurred in database "EXAMPLES", table "dbo.VOTERS", column 'Age'.

But if you enter valid records into the table, then that will be inserted into the table.

INSERT INTO Voters values(1,'Varma',20,'Delhi');

Now let us see another example where we have tried multiple conditions.

CREATE TABLE Employee(
	EmployeeID int,
    FirstName nvarchar(50) NOT NULL,  
    LastName nvarchar(50) NOT NULL, 
    EMail nvarchar(50),
    Salary int,
    CONSTRAINT CHK_Emp_Salary CHECK(Salary > 2000 AND Salary < 4000))

When we execute the above script, it will create the employee table, but we have also added an SQL CHECK constraint for the salary attribute, which will accept values greater than 2000 and less than 4000. If the user enters any value which is not satisfied the CHECK constraint condition, then SQL will terminate the query, and the below error message will be displayed in the console.

If you execute the below query, then you will get the error message

INSERT INTO Employee2 values(1,'Ram','Varma','admin@softwaretestingo.com',1500);

The INSERT statement conflicted with the CHECK constraint "CHK_Emp_Salary". The conflict occurred in database "EXAMPLES", table "dbo.Employee2", column 'Salary'.
The statement has been terminated.

But if you try to insert the below records, the check constraints conditions will satisfy, allowing you to insert the record.

INSERT INTO Employee2 values(1,'Ram','Varma','admin@softwaretestingo.com',2500);

Now we will try to add the check constraint with multiple columns, for example.

CREATE TABLE Employee1(
    Id int NOT NULL,
    Name varchar(266) NOT NULL,
    City varchar(266) NOT NULL,
    Salary int NOT NULL,
    CONSTRAINT CHK_Employee CHECK (City ='Bangalore' AND Salary>=80000)
);

In the above script, we have applied the check constraint in both City and Salary columns. So only those records will be inserted into the table which has the city Bangalore and Salary greater than equal 80000.

INSERT INTO Employee1 values(1,'Ram','Chennai', 802500);

If you execute the above script, you will get an error message because the salary value is greater than 80000, but the city is not Bangalore, so that you will get the below error message.

The INSERT statement conflicted with the CHECK constraint "CHK_Employee". The conflict occurred in database "EXAMPLES", table "dbo.Employee1".
The statement has been terminated.

So to satisfy the condition, let us make some changes in the city to Bangalore and will try to insert them into the table.

INSERT INTO Employee1 values(1,'Ram','Bangalore', 802500);

By going through the above examples now, we believe you understand why CHECK constraints are an important tool for ensuring data integrity in a database by enforcing rules and constraints that prevent invalid data from being inserted or updated.

Key points to Remember on Check Constraint

• A check constraint is a logical Boolean that returns True or False based on logical operators.
• It can be created with any Boolean expression, such as “Salary > 2000 and Salary < 4000”.
• Multiple columns can be used in a single check constraint.
• A single column can have multiple check constraints.
• Check constraints reject values that evaluate to False.
• If you try to insert a value outside the check constraint value range, the SQL engine throws an error stating that the insert statement conflicted with the CHECK constraint, and the statement was terminated.
• A check constraint does not evaluate a NULL value.
• NULL values can be inserted without restriction; use the NOT NULL constraint to restrict them.
• Check constraints are not validated during the Delete statements.
• Check constraints can be enabled or disabled instead of deleting them.

Default Constraint In SQL

A default constraint is used in a database table to specify a default value for a column. When a new row is inserted into the table, and no value is provided for that column, the default value specified in the constraint is used.

The syntax for the adding default constraint while creating a table:

CREATE TABLE table_name 
(
     column1 datatype [DEFAULT default_value1],
     column2 datatype [DEFAULT default_value2],
        …,   
     columnN datatype [DEFAULT default_valueN]);

Example:

CREATE TABLE employee (
    emp_id INT NOT NULL,
    emp_name VARCHAR (20) NOT NULL, 
    dept_id INT DEFAULT 1000);

When we execute the above script, it will create an employee table where we have configured the dept_id with a default value of 1000. For better understanding, let us try to insert a record into the table without mentioning any value for the dept_id column.

Let us execute the below script to insert the records:

INSERT INTO employee01 (emp_id,emp_name)VALUES (001, 'Ramesh');

But when we execute the above script, you can notice we have passed values for emp_id and emp_name. But when we execute the select query, you can find that the default value is set for the dept_id column.

But we can also add the default constraint to an existing table. To do so, we have to use the Alter statement. Here is the syntax:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
DEFAULT default_value FOR column_name;

Suppose we have a table, and previously there was no Default constraint. So when we enter the first record, then a null value appears.

But let us try to add a default constraint for the dept_id column, and after that will see how the records appear.

ALTER TABLE employee01
ADD CONSTRAINT df_dept_id
DEFAULT 100 FOR dept_id;

After adding the default constraint, let us insert some records.

Delete Default Constraint: Using the alter statement, we can also remove the default constraint from a table. Here is the syntax:

ALTER TABLE table_name
ALTER COLUMN column_name DROP DEFAULT;

When we try to drop the default constraint with the above syntax, we get the below error message.

Cannot drop a default constraint by DROP DEFAULT statement. Use ALTER TABLE to drop a constraint default.

When we tried to find a solution for this, I got an update from the official website where it is mentioned that this drop default constraint will be removed from the next versions of SQL Server. If you want more updates on that, you can read this article.

DROP Constraint

In database management systems, a constraint is a rule or condition enforced to maintain the integrity, consistency, and accuracy of the data stored in a database. Constraints can be different types, such as unique, primary key, foreign key, check, and not null.

But when a constraint is no longer required or needs to be removed, you can use the “DROP CONSTRAINT” command to delete it from the database schema. This includes UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK constraints which can all be removed with the SQL DROP CONSTRAINT command.

Here is the syntax of various use cases of DROP Constraint:

Syntax For Drop Foreign Key Constraint

ALTER TABLE child_table_name
DROP CONSTRAINT foreignkey_name;

Syntax For Drop Primary Key Constraint

ALTER TABLE table_name
DROP CONSTRAINT primarykey_name;

Syntax For Drop Composite Key Constraint

ALTER TABLE table_name
DROP CONSTRAINT existing_compkey_name;

Syntax For Drop Unique Key Constraint

ALTER TABLE table_name
DROP CONSTRAINT UK_name
UNIQUE (column_name);

Syntax For Drop Check Key Constraint

ALTER TABLE table_name
DROP CHECK chk_name
CHECK (condition);

Conclusion:

This article provided an overview of constraints in SQL. Constraints are useful for enforcing rules on the data you enter into your database, which helps ensure meaningful results when using the information it contains. SQL constraints come in different forms, including primary key, foreign key, unique, not null, and check constraints, each with its specific use case.

If you’d like to continue learning more about SQL, refer to our detailed guide, SQL Tutorial For Beginners, where we have shared each SQL server topic in detail.