The SQL Auto Increment is used to automatically add unique sequential values into a column of a table.

We usually define the Auto Increment on a column while creating a table. And when we insert new records into the table, the unique values are added to them.

When we use Auto Increment on a table column, there is no need to insert NOT NULL values to that column. If we provide such values, they will overwrite the unique identities and the increment will be continued (only) on the NULL values (if any); causing ambiguity on the data.

Different RDBMS support the Auto Increment feature in different ways.

Auto Increment in MySQL

In MySQL, you can add the auto-increment feature to a column of a table using the attribute named AUTO_INCREMENT.

By default, when we define the AUTO_INCREMENT attribute on a column, the unique values are generated from “1”; and for each new record we enter into the table, the values in the column will increment by 1. Thus, the first record inserted will have a value of 1, the second record will have a value of 2, and so on.

Syntax

Following is the syntax to add AUTO_INCREMENT attribute to a column of a table in MySQL −

CREATE TABLE table_name(
   column1 datatype AUTO_INCREMENT,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype
);

Example

In the query to we are creating a table named CUSTOMERS and adding the AUTO_INCREMENT to the column named ID −

CREATE TABLE CUSTOMERS(
   ID INT PRIMARY KEY AUTO_INCREMENT,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2)
);

Now, let us insert values into the CUSTOMERS table using the INSERT statement −

INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES
("Ramesh", 32, "Ahmedabad", 2000.00),
("Khilan", 25, "Delhi", 1500.00),
("Kaushik", 23, "Kota", 2000.00),
("Chaitali", 25, "Mumbai", 6500.00);

Verification

To verify this, you need to retrieve the contents of the CUSTOMERS using the SELECT query as −

SELECT * FROM CUSTOMERS;

Output

Following is the output of the above query, here you can observe that the ID values are generated automatically −

AUTO_INCREMENT on Existing Columns

MySQL also allows you to implement the AUTO_INCREMENT attribute on an existing table, using the ALTER TABLE statement.

Following query starts incrementing the ID values from 5 in the CUSTOMERS table CUSTOMERS −

ALTER TABLE CUSTOMERS AUTO_INCREMENT = 100;

Now, let us insert more records to see if the ID values are auto incremented.

INSERT INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES
("Hardik", 27, "Bhopal", 8500.00),
("Komal", 22, "MP", 4500.00),
("Muffy", 24, "Indore", 10000.00);

To view the above table data, we use the following SELECT query −

SELECT * FROM CUSTOMERS;

Output

The output of the above query is shown below. It shows the auto increment in action. We are getting the ID values of the newly inserted records begins at 100.

Auto Increment in SQL Server

In SQL Server, there is no direct command/query to perform Auto Increment. Instead, we use the IDENTITY() property. This property works similar to the AUTO_INCREMENT attribute in MySQL. It generates unique, sequential numbers automatically and it is mostly used on the PRIMARY KEY constraint.

Syntax

Following is the basic syntax of IDENTITY() property in SQL Server −

CREATE TABLE table_name (
   column1 datatype IDENTITY [(seed, increment)],
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype
);

This property accepts two parameters. The same are described below:

• seed: It sets the starting value for the auto-incrementing column.
• increment: It specifies how much the value increases by for each new row.

Example

In the following example, we are using the IDENTITY() property on the ID column of table named CUSTOMERS −

CREATE TABLE CUSTOMERS(
   ID INT PRIMARY KEY IDENTITY(1,1),
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2)
);

After creating the table, we are inserting some records using the following query −

INSERT INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES
(''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(''Khilan'', 25, ''Delhi'', 1500.00),
(''Kaushik'', 23, ''Kota'', 2000.00),
(''Chaitali'', 25, ''Mumbai'', 6500.00);

To view the table data, we use the following SELECT query −

SELECT * FROM CUSTOMERS;

Output

Following is an output of the above query, where ID values are generated automatically −

SQL provides multiple datatypes and functions to handle Date and Time values in a database. This is because Date and Time values are represented in various formats. For instance, there are two common ways to represent a date value: DD/MM/YYYY and MM/DD/YYYY. Similarly, there is more than a single way to represent time values.

For a database to recognize such data given in any format, we make use of multiple datatypes and functions.

The only tricky part about storing the Date and Time data in a database is making sure that the values are inserted in the tables with the same format as the datatype.

Different database systems use different datatypes and functions to store and handle the Date and Time data.

Date & Time Datatypes in SQL

Date and time datatypes are used in SQL to store date and time values in various formats. The datatypes available in SQL are listed below.

Example

In the following example, let us create a table named SALES_DETAILS which accepts only date and time values in different formats.

CREATE TABLE SALES_DETAILS(
   orderDate DATE,
   shippingDate DATETIME,
   deliveredDate TIMESTAMP,
   time TIME
);

To insert values into this table, use the following query −

INSERT INTO SALES_DETAILS VALUES
(''2023-02-01'', ''2023-02-01 :10:00'',''2023-02-03 :18:00'', ''18:00'');

Output

The table will be created as follows −

Date & Time Functions in SQL

SQL also provides multiple functions to handle date and time values.

For instance, there are different functions to retrieve the current timestamp in different formats. Let us see some of such functions below −

CURDATE() Function

To get the current date, we use the CURDATE() function in MySQL. The format of the resultant date will be ”YYYY-MM-DD” (string) or YYYYMMMDD (numeric).

SELECT CURDATE();

Output

When we execute the above query, we get the current days date −

NOW() Function

The MySQL NOW() function will retrieve the current date and time value as a timestamp based on the context and, the value returned will be in either of the two formats: ”YYYY-MM-DD hh:mm:ss” and ”YYYYMMDDhhmmss”.

SELECT NOW();

Output

When we execute the above SQL query, we get the current date with time as follow −

CURRENT_TIMESTAMP() Function

The MySQL CURRENT_TIMESTAMP() function is used to get the current timestamp. The value returned will be in ”YYYY-MM-DD hh:mm:ss” (string) or YYYYMMDDhhmmss (numeric) format. This function is a synonym for NOW().

SELECT CURRENT_TIMESTAMP();

Output

When we run the above SQL query, we get the following output −

Sequences in SQL are database objects that generate a sequence of unique integer values. They are frequently used in databases because many applications require that each row in a table must contain unique values and sequences provide an easy way to generate them.

Sequences are a feature of many SQL database management systems, such as Oracle, PostgreSQL, SQL server, and IBM DB2.

MySQL does not support the CREATE SEQUENCE statement to create sequences for table rows or columns. Instead, we can use AUTO_INCREMENT attribute.

Sequences in MySQL

In MySQL, we use the AUTO_INCREMENT attribute to generate unique integer values (sequences) for a column. By default, the sequence starts with an initial value of 1 and increments by 1 for each new row.

Syntax

Following is the syntax of AUTO_INCREMENT attribute in MySQL −

CREATE TABLE table_name (
	column1 datatype AUTO_INCREMENT,
	column2 datatype,
	column3 datatype,
	...
	columnN datatype
);

Example

In the following example, we are creating a table named CUSTOMERS. In addition to that, we are defining AUTO_INCREMENT on ID column of the table.

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Here, we are adding some records into the above created table −

INSERT INTO CUSTOMERS VALUES
(NULL, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(NULL, ''Khilan'', 25, ''Delhi'', 1500.00),
(NULL, ''Kaushik'', 23, ''Kota'', 2000.00),
(NULL, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(NULL, ''Hardik'', 27, ''Bhopal'', 8500.00),
(NULL, ''Komal'', 22, ''Hyderabad'', 4500.00),
(NULL, ''Muffy'', 24, ''Indore'', 10000.00);

The table will be created as follows −

As we can see in the above table, the values in the ID column are auto incremented.

Starting a Sequence at a Particular Value in MySQL

By default, MySQL sequences start from 1. To start a sequence with a different value, we use the AUTO_INCREMENT in combination with the ALTER statement.

Syntax

Following is the syntax to start the sequence with different value −

ALTER TABLE table_name AUTO_INCREMENT = value;

In the following query, we are creating a table named BUYERS with AUTO_INCREMENT defined on the ID column.

CREATE TABLE BUYERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Here, we are making the sequence start with 100 using the ALTER Statement as shown below −

ALTER TABLE BUYERS AUTO_INCREMENT=100;

Now, we are adding records into the BUYERS table using the INSERT INTO statement −

INSERT INTO BUYERS VALUES
(''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(''Khilan'', 25, ''Delhi'', 1500.00),
(''Kaushik'', 23, ''Kota'', 2000.00),
(''Chaitali'', 25, ''Mumbai'', 6500.00),
(''Hardik'', 27, ''Bhopal'', 8500.00),
(''Komal'', 22, ''Hyderabad'', 4500.00),
(''Muffy'', 24, ''Indore'', 10000.00);

The table will be created as −

As observed in the table above, the values in the “ID” column begin with 100 instead of 1.

Sequences in SQL Server

In SQL server, a sequence can be created using the CREATE SEQUENCE statement. The statement specifies the name of the sequence, the starting value, the increment, and other properties of the sequence.

Syntax

Following is the syntax to create a sequence in SQL −

CREATE SEQUENCE Sequence_Name
START WITH Initial_Value
INCREMENT BY Increment_Value
MINVALUE Minimum_Value
MAXVALUE Maximum_Value
CYCLE|NOCYCLE;

Here,

• Sequence_Name − This specifies the name of the sequence.

• Initial_Value − This specifies the starting value from where the sequence should start.

• Increment_Value − This specifies the value by which the sequence will increment by itself. This can be valued positively or negatively.

• Minimum_Value − This specifies the minimum value of the sequence.

• Maximum_Value − This specifies the maximum value of the sequence.

• Cycle − When the sequence reaches its Maximum_Value, it starts again from the beginning.

• Nocycle − An exception will be thrown if the sequence exceeds the Maximum_Value.

Example

First of all, let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
);

We are inserting some records in the above-created table using INSERT INTO statement as shown in the query below −

INSERT INTO CUSTOMERS VALUES
(NULL, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(NULL, ''Khilan'', 25, ''Delhi'', 1500.00),
(NULL, ''Kaushik'', 23, ''Kota'', 2000.00),
(NULL, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(NULL, ''Hardik'', 27, ''Bhopal'', 8500.00),
(NULL, ''Komal'', 22, ''Hyderabad'', 4500.00),
(NULL, ''Muffy'', 24, ''Indore'', 10000.00 );

The table is successfully created in the SQL database.

Now, create a sequence using the following query −

CREATE SEQUENCE My_Sequence AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 7
CYCLE;

In the above query, the sequence is named My_Sequence and it starts with the value 1 and increments by 1 each time a value is generated. The sequence has a maximum value of 5 and cycles back to the starting value when it reaches the maximum value.

Once the sequence is created, it can be used to generate unique integer values. Now, let us update the data in the ID column of the CUSTOMERS table using the following query −

UPDATE CUSTOMERS SET ID = NEXT VALUE FOR my_Sequence;

Output

When you execute the above query, the output is obtained as follows −

(7 rows affected)

Verification

Let us verify whether is sequence is updated in the ID column of the table or not using the following query −

SELECT * FROM CUSTOMERS;

The table will be displayed as −

Sometimes, tables or result sets contain duplicate records. While in most cases, duplicates are allowed, there are situations where it is necessary to prevent duplicate records and remove them from a database table.

Why is Handling Duplicates in SQL Necessary?

Handling duplicates in an SQL database becomes necessary to prevent the following consequences −

• The existence of duplicates in an organizational database will lead to logical errors.

• Duplicate data occupies space in the storage, which leads to decrease in usage efficiency of a database.

• Due to the increased use of resources, the overall cost of the handling resources rises.

• With increase in logical errors due to the presence of duplicates, the conclusions derived from data analysis in a database will also be erroneous.

This chapter will describe how to prevent the occurrence of duplicate records in a table and how to remove the already existing duplicate records.

Preventing Duplicate Entries

To prevent the entry of duplicate records into a table, we can define a PRIMARY KEY or a UNIQUE Index on the relevant fields. These database constraints ensure that each entry in the specified column or set of columns is unique.

Example

Let us create a CUSTOMERS table using the following query −

CREATE TABLE CUSTOMERS (
   FIRST_NAME CHAR(20),
   LAST_NAME CHAR(20),
   SEX CHAR(10)
);

As we have not defined any constraints on the table, duplicate records can be inserted into it. To prevent such cases, add a PRIMARY KEY constraint on relevant fields (say LAST_NAME and FIRST_NAME together) −

ALTER TABLE CUSTOMERS
ADD PRIMARY KEY (LAST_NAME, FIRST_NAME);

Using INSERT IGNORE Query:

Alternatively, we can use the INSERT IGNORE statement to insert records without generating an error for duplicates as shown below −

INSERT IGNORE INTO CUSTOMERS (LAST_NAME, FIRST_NAME) VALUES
( ''Jay'', ''Thomas''),
( ''Jay'', ''Thomas'');

As you can see below, the table will only consist of a single record (ignoring the duplicate value).

Using REPLACE Query:

Or, use the REPLACE statement to replace duplicates as shown in the following query −

REPLACE INTO CUSTOMERS (LAST_NAME, FIRST_NAME) VALUES
( ''Ajay'', ''Kumar''),
( ''Ajay'', ''Kumar'');

The table will contain the following records −

The choice between the INSERT IGNORE and REPLACE statements should be made based on the desired duplicate-handling behaviour. The INSERT IGNORE statement retains the first set of duplicate records and discards any subsequent duplicates. Conversely, the REPLACE statement preserves the last set of duplicates and erases any earlier ones.

Using UNIQUE Constraint:

Another way to enforce uniqueness in a table is by adding a UNIQUE constraint rather than a PRIMARY KEY constraint −

CREATE TABLE BUYERS (
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20) NOT NULL,
   SEX CHAR(10),
   UNIQUE (LAST_NAME, FIRST_NAME)
);

Counting and Identifying Duplicates

To count and identify duplicate records based on specific columns, we can use the COUNT function and GROUP BY clause.

Example

Following is the query to count duplicate records with FIRST_NAME and LAST_NAME in the BUYERS −

SELECT COUNT(*) as repetitions, LAST_NAME, FIRST_NAME
FROM BUYERS
GROUP BY LAST_NAME, FIRST_NAME
HAVING repetitions > 1;

This query will return a list of all the duplicate records in the PERSON_TABLE table. To identify sets of values that are duplicated, follow the steps given below −

• Determine which columns contain the values that may be duplicated.

• List those columns in the column selection list, along with the COUNT(*).

• List the columns in the GROUP BY clause as well.

• Add a HAVING clause that eliminates the unique values by requiring the group counts to be greater than one.

Eliminating Duplicates from a Table

We can use the DISTINCT keyword along with the SELECT statement to retrieve unique records from a table.

SELECT DISTINCT LAST_NAME, FIRST_NAME
FROM BUYERS
ORDER BY LAST_NAME;

Alternatively, you can include a GROUP BY clause specifying the columns you are selecting to eliminate duplicates −

SELECT LAST_NAME, FIRST_NAME
FROM BUYERS
GROUP BY LAST_NAME, FIRST_NAME;

SQL Subqueries

An SQL Subquery, is a SELECT query within another query. It is also known as Inner query or Nested query and the query containing it is the outer query.

The outer query can contain the SELECT, INSERT, UPDATE, and DELETE statements. We can use the subquery as a column expression, as a condition in SQL clauses, and with operators like =, >, <, >=, <=, IN, BETWEEN, etc.

Rules to be followed

Following are the rules to be followed while writing subqueries −

• Subqueries must be enclosed within parentheses.

• Subqueries can be nested within another subquery.

• A subquery must contain the SELECT query and the FROM clause always.

• A subquery consists of all the clauses an ordinary SELECT clause can contain: GROUP BY, WHERE, HAVING, DISTINCT, TOP/LIMIT, etc. However, an ORDER BY clause is only used when a TOP clause is specified. It can”t include COMPUTE or FOR BROWSE clause.

• A subquery can return a single value, a single row, a single column, or a whole table. They are called scalar subqueries.

Subqueries with the SELECT Statement

Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows −

SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE  column_name
OPERATOR (SELECT column_name [,column_name ] FROM table1 [, table2 ] [WHERE]);

Example

In the following query, we are creating a table named CUSTOMERS −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Here, we are inserting records into the above-created table using INSERT INTO statement −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(2, ''Khilan'', 25, ''Delhi'', 1500.00),
(3, ''Kaushik'', 23, ''Kota'', 2000.00),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00),
(7, ''Muffy'', 24, ''Indore'', 10000.00);

The table is displayed as −

Now, let us check the following subquery with a SELECT statement.

SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500);

This would produce the following result −

Subqueries with the INSERT Statement

We can also use the subqueries along with the INSERT statements. The data returned by the subquery is inserted into another table.

The basic syntax is as follows −

INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ] FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

Example

In the following example, we are creating another table CUSTOMERS_BKP with similar structure as CUSTOMERS table −

CREATE TABLE CUSTOMERS_BKP (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now to copy the complete records of CUSTOMERS table into the CUSTOMERS_BKP table, we can use the following query −

INSERT INTO CUSTOMERS_BKP
SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID FROM CUSTOMERS);

The above query produces the following output −

Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

Using the SELECT statement, we can verify whether the records from CUSTOMERS table have been inserted into CUSTOMERS_BKP table or not −

SELECT * FROM CUSTOMERS_BKP;

The table will be displayed as −

Subqueries with the UPDATE Statement

A subquery can also be used with the UPDATE statement. You can update single or multiple columns in a table using a subquery.

The basic syntax is as follows −

UPDATE table
SET column_name = new_value
[WHERE OPERATOR [VALUE](SELECT COLUMN_NAME FROM TABLE_NAME [WHERE]);

Example

We have the CUSTOMERS_BKP table available which is backup of CUSTOMERS table. The following example updates SALARY by 0.25 times in the CUSTOMERS table for all the customers whose AGE is greater than or equal to 27.

UPDATE CUSTOMERS
SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 );

Following is the output of the above query −

Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Verification

This would impact two rows and if you verify the contents of the CUSTOMERS using the SELECT statement as shown below.

SELECT * FROM CUSTOMERS;

The table will be displayed as −

Subqueries with the DELETE Statement

The subquery can be used with the DELETE statement as well; like with any other statements mentioned above.

The basic syntax is as follows −

DELETE FROM TABLE_NAME
[WHERE OPERATOR [ VALUE ](SELECT COLUMN_NAME FROM TABLE_NAME)[WHERE)];

Example

We have a CUSTOMERS_BKP table available which is a backup of the CUSTOMERS table. The following example deletes the records from the CUSTOMERS table for all the customers whose AGE is greater than or equal to 27.

DELETE FROM CUSTOMERS
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 );

The above query generate the following output −

OK, 2 rows affected (0.01 sec)

Verification

If you verify the contents of the CUSTOMERS table using the SELECT statement as shown below.

SELECT * FROM CUSTOMERS;

The table will be displayed as −

SQL Transactions

A transaction is a unit or sequence of work that is performed on a database. Transactions are accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

A transaction is the propagation of one or more changes to the database. For example, if you are creating, updating or deleting a record from the table, then you are performing a transaction on that table. It is important to control these transactions to ensure the data integrity and to handle database errors.

Practically, you will club many SQL queries into a group and you will execute all of them together as a part of a transaction.

Properties of Transactions

Transactions have the following four standard properties, usually referred to by the acronym ACID.

• Atomicity − ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state.

• Consistency − ensures that the database properly changes states upon a successfully committed transaction.

• Isolation − enables transactions to operate independently of and transparent to each other.

• Durability − ensures that the result or effect of a committed transaction persists in case of a system failure.

Transactional Control Commands

Transactional control commands are only used with the DML Commands such as – INSERT, UPDATE and DELETE. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database. Following commands are used to control transactions.

• COMMIT − to save the changes.

• ROLLBACK − to roll back the changes.

• SAVEPOINT − creates points within the groups of transactions in which to ROLLBACK.

• SET TRANSACTION − Places a name on a transaction.

The COMMIT Command

The COMMIT command is the transactional command used to save changes invoked by a transaction. It saves all the transactions occurred on the database since the last COMMIT or ROLLBACK.

The syntax for the COMMIT command is as follows.

COMMIT;

Example

Firstly, let us create a table names CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

We are inserting some records into the above-created table −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(2, ''Khilan'', 25, ''Delhi'', 1500.00),
(3, ''Kaushik'', 23, ''Kota'', 2000.00),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00),
(7, ''Muffy'', 24, ''Indore'', 10000.00);

The table will be created as follows −

Following query would delete those records from the table which have AGE as 25 and then COMMIT the changes in the database.

DELETE FROM CUSTOMERS WHERE AGE = 25;
COMMIT;

Verification

The two rows from the table would be deleted and if you verify the contents of the CUSTOMERS table using the SELECT statement as −

SELECT * FROM CUSTOMERS;

The table will be displayed as follows −

The ROLLBACK Command

The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database. This command can only undo transactions since the last COMMIT or ROLLBACK.

The syntax for a ROLLBACK command is as follows −

ROLLBACK;

Example

Consider the CUSTOMERS table having the following records −

Following query would delete those records from the table where the AGE value is 25 and then ROLLBACK the changes in the database.

DELETE FROM CUSTOMERS WHERE AGE = 25;
ROLLBACK;

Verification

The delete operation would not impact the table and the SELECT statement would produce the following result.

The SAVEPOINT Command

A SAVEPOINT is a logical rollback point in a transaction.

Usually, when you execute the ROLLBACK command, it undoes the changes until the last COMMIT. But, if you create save points you can partially roll the transaction back to these points. You can create multiple save points between two commits.

The syntax to create a SAVEPOINT among the transactions is as shown below.

SAVEPOINT savepoint_name;

Then, to roll back to the SAVEPOINT created, you can use the following syntax −

ROLLBACK TO savepoint_name;

Example

Following is an example where you plan to delete the three different records from the CUSTOMERS table. You want to create a SAVEPOINT before each delete, so that you can ROLLBACK to any SAVEPOINT at any time to return the appropriate data to its original state.

Consider the CUSTOMERS table having the following records.

The following code block contains the series of operations.

SAVEPOINT SP1;
Query OK, 0 rows affected (0.00 sec)

DELETE FROM CUSTOMERS WHERE ID=1;
Query OK, 1 row affected (0.01 sec)

SAVEPOINT SP2;
Query OK, 0 rows affected (0.00 sec)

DELETE FROM CUSTOMERS WHERE ID=2;
Query OK, 0 rows affected (0.00 sec)

SAVEPOINT SP3;
Query OK, 0 rows affected (0.00 sec)

DELETE FROM CUSTOMERS WHERE ID=3;
Query OK, 1 row affected (0.01 sec)

Now that the three deletions have taken place, let us assume that you have changed your mind and decided to ROLLBACK to the SAVEPOINT that you identified as SP2. Because SP2 was created after the first deletion, the last two deletions are undone −

ROLLBACK TO SP2;

Verification

If you display the CUSTOMERS table, you can notice that only the first deletion took place since you rolled back to SP2.

The RELEASE SAVEPOINT Command

The RELEASE SAVEPOINT command is used to remove an existing SAVEPOINT.

The syntax for a RELEASE SAVEPOINT command is as follows.

RELEASE SAVEPOINT SAVEPOINT_NAME;

Once a SAVEPOINT has been released, you can no longer use the ROLLBACK command to undo transactions performed since the last SAVEPOINT.

The SET TRANSACTION Command

The SET TRANSACTION command can be used to initiate a database transaction. This command is used to specify characteristics for the transaction that follows. For example, you can specify a transaction to be read only or read write.

Syntax

The syntax for a SET TRANSACTION command is as follows.

SET TRANSACTION [ READ WRITE | READ ONLY ];

SQL Stored Procedures

An SQL stored procedure is a group of pre-compiled SQL statements (prepared SQL code) that can be reused by simply calling it whenever needed.

It can be used to perform a wide range of database operations such as inserting, updating, or deleting data, generating reports, and performing complex calculations. Stored procedures are very useful because they allow you to encapsulate (bundle) a set of SQL statements as a single unit and execute them repeatedly with different parameters, making it easy to manage and reuse the code.

Procedures have similar structure as functions: they accept parameters and perform operations when we call them. But, the difference between them is that SQL stored procedures are simpler to write or create, whereas functions have a more rigid structure and support fewer clauses.

Syntax

The basic syntax to create an SQL stored procedure is as follows −

DELIMITER //
CREATE PROCEDURE procedure_name(parameter1 datatype, parameter2 datatype, ...)
BEGIN
   -- SQL statements to be executed
END
DELIMITER ;

• The CREATE PROCEDURE statement is used to create the procedure. We can define any number of input parameters as per the requirement.

• The SQL statements that make up the procedure are placed between the BEGIN and END keywords.

Creating a Procedure

We can create a stored procedure using the CREATE PROCEDURE statement in SQL. Following are the simple steps for creating a stored procedure −

• Choose a name for the procedure.

• Write the SQL code for the procedure.

• We can then test the stored procedure by executing it with different input parameters.

Example

To understand it better let us consider the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(2, ''Khilan'', 25, ''Delhi'', 1500.00),
(3, ''Kaushik'', 23, ''Kota'', 2000.00),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00),
(7, ''Muffy'', 24, ''Indore'', 10000.00);

The table will be created as −

Now, let us look at a simple example of creating a stored procedure that takes an input parameter and returns a result set.

In the following query, we are creating the stored procedure with the name GetCustomerInfo. then we provide it with a single input parameter called @CutomerAge. The stored procedure then selects all records from the CUSTOMERS table where the value of the CutomerAge matches the input parameter.

DELIMITER //
CREATE PROCEDURE GetCustomerInfo(IN CustomerAge INT)
   BEGIN
      SELECT * FROM CUSTOMERS WHERE AGE = CustomerAge;
   END //
DELIMITER ;

Output

This would produce the following result −

Query OK, 0 rows affected (0.01 sec)

Verification

We can test the stored procedure by executing it using the CALL statement as shown below −

CALL GetCustomerInfo(25);

This will return all columns from the CUSTOMERS table where the customers age is 25.

Stored Procedure Parameter Types

Stored procedures in a database system can have different types of parameters, which are placeholders for values that will be passed to the stored procedure when it is executed. Following are the different types of stored procedure parameters in SQL −

Procedure with IN parameter

IN is the default parameter of the procedure that will receive input values. We can pass the values as arguments when the stored procedure is being called.

These values are read-only, so they cannot be modified by the stored procedure.

Example

In the following query, we are creating a stored procedure that takes a customer ID as an input parameter and returns the corresponding customer salary.

The procedure body simply performs a SELECT statement to retrieve the “Salary” column from the “CUSTOMERS” table, where the “CustomerID” matches the input parameter.

DELIMITER //
CREATE PROCEDURE GetCustomerSalary(IN CustomerID Int)
   BEGIN
      SELECT SALARY FROM CUSTOMERS WHERE ID = CustomerID;
   END //
DELIMITER ;

Output

This would produce the following result −

Query OK, 0 rows affected (0.01 sec)

Verification

We can test it by executing it with different ID as an input parameter as shown in the query below −

CALL GetCustomerSalary(6);

This will return the salary for the customer with an ID of 6, assuming there is a corresponding row in the CUSTOMERS table −

Procedure with OUT parameter

The OUT parameter is used to return the output value from the procedure.

Note that when using an OUT parameter, we must specify the keyword OUT before the parameter name when passing it to the stored procedure. This tells the SQL database that the parameter is an output parameter and should be assigned with a value in the stored procedure.

Example

In the following query we are creating a stored procedure that used to count the number of records of customer having same age and assign this count to the ”total” variable which holds the number of records.

The procedure body performs a SELECT statement to get the count of records having same age from the “CUSTOMERS” table

DELIMITER //
CREATE PROCEDURE GetDetail(OUT total INT)
   BEGIN
      SELECT COUNT(AGE) INTO total FROM CUSTOMERS
      WHERE AGE = 25;
   END //
DELIMITER ;

Calling the created procedure and passing the ”total” parameter

CALL GetDetail(@total);

Here, we are using the SELECT statement and getting the count −

SELECT @total;

Output

This would produce the following result −

Verification

To verify weather the procedure is created, we can use the following query −

SHOW CREATE PROCEDURE GetDetails;

Procedure with INOUT parameter

The INOUT parameter is a combination of an IN parameter and an OUT parameter. You can pass data into the stored procedure and receive data from the stored procedure using the same parameter.

To declare an INOUT parameter in a stored procedure, we need to specify the INOUT keyword before the parameter name.

Example

In the following query, we provide two INOUT parameters to the stored procedure: cust_id and curr_Salary. These two are used as both an input and output parameters.

The stored procedure first retrieves the current salary of the customer from the database using the cust_id parameter. It then increases the salary by 10% and updates the customers salary in the database using the same parameter.

DELIMITER //
CREATE PROCEDURE increaseSalary(INOUT Cust_Id Int,  INOUT curr_Salary Int)
   BEGIN
      SELECT SALARY INTO curr_Salary From CUSTOMERS Where ID = Cust_Id;
      SET curr_Salary = curr_Salary * 1.1;
      Update CUSTOMERS SET SALARY = curr_Salary Where ID = Cust_Id;
   END //
DELIMITER ;

Output

This would produce the following result −

Query OK, 0 rows affected (0.01 sec)

Verification

We can test it by executing it with different ID or input parameters as shown in the query below −

SET @customerID = 1;
SET @salary = 0.0;
CALL increaseSalary(@customerID, @salary);

Following is Query to select the updated salary from the stored procedure

SELECT @salary AS updated_salary;

The result-set is obtained as −

Advantages of Stored Procedures

Following are the advantages of stored procedures −

• Improved Performance: Stored procedures are pre-compiled and stored on the server, so they can be executed more quickly than SQL statements that are sent from client applications.

• Code Reuse: Stored procedures can be called from different client applications, which means that the same code can be reused across different applications. This reduces development time and maintenance costs.

• Reduced Network Traffic: Because stored procedures are executed on the server, only the results are returned to the client, which reduces network traffic and improves application performance.

• Better Security: Stored procedures can be used to enforce security rules and prevent unauthorized access to sensitive data. They can also limit the actions that can be performed by users, making it easier to maintain data integrity and consistency.

• Simplified Maintenance: By storing SQL code in a single location, it becomes easier to maintain and update the code. This makes it easier to fix bugs, add new functionality, and optimize performance.

Drawbacks of Stored Procedures

Following are the disadvantages of stored procedures −

• Increased Overhead: Stored procedures can consume more server resources than simple SQL statements, particularly when they are used frequently or for complex operations.

• Limited Portability: Stored procedures are often specific to a particular database management system (DBMS), which means they may not be easily portable to other DBMSs.

• Debugging Challenges: Debugging stored procedures can be more challenging than debugging simple SQL statements, particularly when there are multiple layers of code involved.

• Security Risks: If stored procedures are not written correctly, they can pose a security risk, particularly if they are used to access sensitive data or to perform actions that could compromise the integrity of the database.

SQL uses the term NULL to represent a non-existent data value in the database. These values are not the same as an empty string or a zero. They don”t hold any space in the database and are used to signify the absence of a value or the unknown value in a data field.

Some common reasons why a value may be NULL −

• The value may not be provided during the data entry.

• The value is not yet known.

Since the NULL values are basically non-existent, you cannot use comparison operators such as = , <, or > with them. However, you can check if a value is NULL using the IS NULL, “NOT NULL” or IS NOT NULL operators.

Creating a Table without NULL Values

NULL values can be inserted in any column of a table as they are not associated with any specific data type. However, when a column is defined with the “NOT NULL” keyword, an error is raised whenever you try to insert NULL values into that specific column.

Syntax

The basic syntax of NOT NULL while creating a table is as follows −

CREATE TABLE table-name (
   column1 datatype NOT NULL,
   column2 datatype NOT NULL,
   ...
   columnN datatype
);

Here, NOT NULL signifies that column should always accept an explicit value of the given data type. You can insert NULL values into the columns where we did not use NOT NULL.

Example

Let us create a table with the name CUSTOMERS in the SQL database using the CREATE statement as shown in the query below −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25) ,
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Let us insert some values into the above created table using the following query −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(2, ''Khilan'', 25, ''Delhi'', 1500.00),
(3, ''Kaushik'', 23, ''Kota'', 2000.00),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00),
(6, ''Komal'', 22, ''Hyderabad'', NULL),
(7, ''Muffy'', 24, ''Indore'', NULL);

The table is successfully created in the database.

Now, let us retrieve the records present in the table that are not null using the IS NOT NULL operator −

SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE SALARY IS NOT NULL;

The above query would produce the following result −

You can also retrieve the NULL records present in the table using IS NULL operator in the SELECT query as shown below −

SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE SALARY IS NULL;

The above query would produce the following result −

Updating NULL Values in a Table

You can update the NULL values present in a table using the UPDATE statement in SQL. To do so, you can use the IS NULL operator in your WHERE clause to filter the rows containing NULL values and then set the new value using the SET keyword.

Example

Consider the previously created table and update the NULL value(s) present in the table using the UPDATE statement as shown below −

UPDATE CUSTOMERS SET SALARY = 9000 WHERE SALARY IS NULL;

Output

When you execute the above query, the output is obtained as follows −

Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Verification

Let us verify whether the specified record(s) in the table is updated or not using the following query −

SELECT * FROM CUSTOMERS;

On executing the above query, the output is displayed as follows −

Deleting Records with NULL Values

You can delete records containing NULL values from a table using the DELETE FROM statement. You first check whether the table consists of NULL values using the IS NULL operator in WHERE clause and delete the records that are filtered.

Example

Consider the previously created CUSTOMERS table and delete the NULL value(s) present in the table using the DELETE statement as shown below −

DELETE FROM CUSTOMERS WHERE SALARY IS NULL;

Output

When you execute the above query, the output is obtained as follows −

Query OK, 2 rows affected (0.01 sec)

Verification

Let us verify whether the filtered record(s) in the table is deleted or not, by displaying the table using a SELECT statement.

SELECT * FROM CUSTOMERS;

The table will be displayed as −

The SQL DEFAULT Constraint

The SQL DEFAULT Constraint is used to specify the default value for a column of a table. We usually set default value while creating the table.

The default values are treated as the column values if no values are provided while inserting the data, ensuring that the column will always have a value. We can specify default values for multiple columns in an SQL table.

Syntax

Following is the syntax of the SQL DEFAULT Constraint −

CREATE TABLE table_name (
   column1 datatype DEFAULT default_value,
   column2 datatype DEFAULT default_value,
   column3 datatype,
   .....
   columnN datatype
);

Example

In the following query we are creating the CUSTOMERS table using the CREATE TABLE statement. Here, we are adding a default constraint to the columns NAME, AGE, ADDRESS, and SALARY −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL DEFAULT ''Ramesh'',
   AGE INT NOT NULL DEFAULT ''32'',
   ADDRESS CHAR (25) DEFAULT ''Ahmedabad'',
   SALARY DECIMAL (18, 2) DEFAULT ''2000'',
   PRIMARY KEY (ID)
);

Following query inserts values into this table using the INSERT statement −

INSERT INTO CUSTOMERS (ID) VALUES (1);
INSERT INTO CUSTOMERS VALUES (2, ''Khilan'', 25, ''Delhi'', 1500.00 );

The table is created with default values in the NAME, AGE, ADDRESS, and SALARY columns for the first row as shown below −

Passing “DEFAULT” as Value

While inserting data into a table, if the column names are not included in the INSERT query, to insert the default value into the record we need to pass “DEFAULT” as a value, as shown below −

INSERT INTO CUSTOMERS VALUES
(3, ''Kaushik'', DEFAULT, DEFAULT, 2000.00),
(4, ''Chaitali'', DEFAULT, DEFAULT, DEFAULT);

The table obtained is as shown below −

Adding Default Constraint to an Existing Column

We can also add default constraints to an existing column of a table using the ALTER TABLE statement. This allows us to modify the structure of existing table by specifying default values, ensuring data consistency in the database.

Syntax

Following is the syntax for adding a default constraint to a column in an existing table −

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT ''default_value

Example

Assume we have created another table named BUYERS using the CREATE TABLE statement as shown below −

CREATE TABLE BUYERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Following query, we adds a default constraint to the ADDRESS column of the BUYERS table.

ALTER TABLE BUYERS ALTER ADDRESS SET DEFAULT ''Delhi

Following INSERT statement inserts a record into the BUYERS table by providing values to all the columns except ADDRESS −

INSERT INTO BUYERS (ID, NAME, AGE, SALARY) VALUES
(01, ''Rahul'', 27, 50000);

Verification

After inserting the record if you retrieve it back, you can observe the default value (“Delhi”) in the address column −

SELECT * FROM BUYERS WHERE ID = 01;

Output

The table obtained is as follows −

Dropping Default Constraint

We can delete the default constraint from a table using the ALTER TABLE… DROP statement.

Syntax

Following is the syntax to delete the default constraint from a table −

ALTER TABLE table_name
ALTER COLUMN column_name DROP DEFAULT;

Example

In here, we are removing the default constraint from the ADDRESS column of the CUSTOMERS table −

ALTER TABLE CUSTOMERS ALTER ADDRESS DROP DEFAULT;

Verification

We can verify the table details (structure) and check whether there is a default constraint or not using the following query −

DESC CUSTOMERS;

The table obtained is as shown below −

The SQL CHECK Constraint

The SQL CHECK constraint is used to add conditions on a column of a table.

Once you add the check constraint on a column, it ensures that the data entered into the column meets the specified conditions. If a particular record does not meet the conditions, the database will prevent you from inserting or updating that record.

Suppose we have a table CUSTOMERS having a column AGE. We can add a CHECK constraint on this column to ensure that the age entered is always a positive number and not greater than 50 years. If someone tries to input a negative age or an age over 50, the database will reject it, ensuring that your data remains accurate and valid.

Check Constraint on Single Column

To add a check constraint on a column level, we have to specify the check constraint just after the column name during table creation.

Syntax

Following is the syntax to specify the check constraint on a single column −

CREATE TABLE table_name (
   column_name data_type CHECK (condition)
);

Example

In the following query, we are creating a table named CUSTOMERS. Here, we are specifying a column-level check constraint on the AGE column, that allows only those records to be inserted where the age value of the customer is greater than “20” −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL CHECK(AGE>=20),
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Verification

To verify whether the check constraint is added to the AGE column, we can use the following query in the MySQL database −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name=''CUSTOMERS

Output

The above query will show all the details of the CUSTOMERS table, including how many columns have check constraints and what constraints we have specified in the table as shown below −

Now, to verify if the CHECK constraint is working properly, let us insert a record into CUSTOMERS where AGE contains a value less than 20 (does not satisfy the given condition) −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 15, ''Ahmedabad'', 2000.00 );

The output of the above query is as shown below −

ERROR 3819 (HY000): Check constraint ''customers_chk_1'' is violated.

Check Constraint on Multiple Columns

We can also add check constraint on multiple columns of a table by specifying the conditions that must be met for the combination of values in those columns.

Suppose we have a table containing the details of products, including their start and end dates. We can add a CHECK constraint that ensures the end date is always greater than or equal to the start date. In this case, the constraint is checking the values in two columns (start date and end date) within the same row to make sure they follow a specific relationship.

Example

In the following example, we are specifying a column-level check constraint on multiple columns (AGE and SALARY) of the CUSTOMERS table. Here, the AGE column will allow only those records where the AGE is greater than or equal to 20, and the SALARY column will allow only those records where the SALARY is greater than 20000 −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL CHECK(AGE >= 20),
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2) CHECK(SALARY >= 20000),
   PRIMARY KEY (ID)
);

Verification

To verify whether the check constraint is applied on both the columns, we can use the following query in the MySQL database −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name=''CUSTOMERS

Output

It will show all the details of the created table, including how many columns have check constraints and what constraints we have specified in the table −

Now, we are inserting values into the CUSTOMERS table where the age is less than 20 and the salary is less than 20000.

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, ''Ramesh'', 15, ''Ahmedabad'', 2000.00 );

The above query throws an error because the values passed in the AGE and the SALARY columns are not satisfying the CHECK constraints −

ERROR 3819 (HY000): Check constraint ''customers_chk_1'' is violated.

Check Constraint at the Table Level

We must use the check constraint before completing the table creation in order to ensure the check constraint at the table level.

Syntax

Following is the syntax to specify the check constraint on the table level −

CREATE TABLE table_name (
   column1 data_type,
   column2 data_type,...,
   CONSTRAINT constraint_name CHECK(column_name condition_value)
);

Example

In the following SQL query, we are creating a table PRODUCTS. In here, we are specifying a table level check constraint on the DATE_OF_ORDER column, that allows only those records to be inserted where the DATE_OF_ORDER is less than (before) “2023-02-09” −

CREATE TABLE PRODUCTS(
   PID INT NOT NULL,
   PNAME VARCHAR(30),
   DELIVERY_CITY VARCHAR(20),
   DATE_OF_ORDER Date NOT NULL,
   PRICE INT,
   PRIMARY KEY(PID),
   CONSTRAINT Constraint_DOO CHECK(DATE_OF_ORDER <= ''2023-02-09'')
);

Verification

We can verify the CHECK constraint on the created table using the following SQL query −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name=''PRODUCTS

Output

It will show all the details of the created table, including how many columns have check constraints on the table level as shown below −

In here, we are inserting values in the PRODUCTS which have the constraint less than “2023-02-09” on the column DATE_OF_ORDER −

INSERT INTO PRODUCTS VALUES
(001, ''Nike Shoe'', ''Ranchi'', ''2023-01-11'', 2000);

Following is the output of the above query −

Query OK, 1 row affected (0.01 sec)

Check Constraint on an Existing Column

We can use the ALTER TABLE statement to add the check constraint on an existing column of the table.

Syntax

Following is the Syntax to add a check-constraint on an existing table −

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK(ColumnName condition_value);

Example

In the following query, we are creating a table named CUSTOMERS −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

To add a check constraint on the AGE column, we are using the following query −

ALTER TABLE CUSTOMERS
ADD CONSTRAINT Constraint_Age CHECK (AGE >= 21);

Verification

To verify whether the check constraint is applied after the table creation, use the following SQL query −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name=''CUSTOMERS

Output

It will display all of the table”s information, including the constraint we added to the age column −

Removing a Check Constraint

If there is a way to add a constraint on a column, then you must also be able to remove the constraint from that column. To do that, you can use the ALTER DROP statement.

Syntax

Following is the syntax to remove a check constraint from the table −

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example

Following example shows how to drop the check constraint from the CUSTOMERS table created above −

ALTER TABLE CUSTOMERS
DROP CONSTRAINT Constraint_Age;

Verification

Using the following SQL query, we are verifying whether the constraint is removed −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name=''CUSTOMERS

Output

We can see that the check constraint added on the age column is removed −