SQL Query to Display All the Existing Constraints on a Table

In SQL, we sometimes need to display all the currently existing constraints on a table. The whole process for doing the same is demonstrated below. For this article, we will be using the Microsoft SQL Server as our database.

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step 2: Use the GeeksForGeeks database. For this use the below command.

Query:

USE GeeksForGeeks

Output:

Step 3: Create a table STUDENT_INFO inside the database GeeksForGeeks. This table has 3 columns namely ROLL_NO, STUDENT_NAME, and BRANCH containing the roll number, name, and branch of various students.

Query:

CREATE TABLE STUDENT_INFO(
ROLL_NO INT,
STUDENT_NAME VARCHAR(10),
BRANCH VARCHAR(5)
);

Output:

Step 4: Display the current constraints applied on the table STUDENT_INFO. We use INFORMATION_SCHEMA.TABLE_CONSTRAINTS to display the constraints. Here, we display the name(CONSTRAINT_NAME) and the type of the constraint(CONSTRAINT_TYPE) for all existing constraints.

Syntax:

SELECT INFORMATION
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='TABLE_NAME';

Query:

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='STUDENT_INFO';

Note – Since our table has no constraints currently, hence no rows are displayed.

Output:

Step 5: Alter the ROLL_NO column using ALTER clause table to set it to NOT NULL. We do this because it is a prerequisite for setting the ROLL_NO as a PRIMARY KEY(done in the next step).

Query:

ALTER TABLE STUDENT_INFO ALTER 
COLUMN ROLL_NO INT NOT NULL;

Output:

Step 6: Add a PRIMARY KEY constraint named C1 to the ROLL_NO column using ALTER clause.

Query:

ALTER TABLE STUDENT_INFO ADD CONSTRAINT 
C1 PRIMARY KEY (ROLL_NO);

Output:

Step 7: Display the current constraints applied on the table STUDENT_INFO.

Query:

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='STUDENT_INFO';

Note: Since our table has only 1 constraint i.e. the PRIMARY KEY constraint currently, hence only 1 row is displayed.

Output:

Step 8: Add a CHECK constraint named BRANCH_CHECK to the BRANCH column using ALTER clause.

Query:

ALTER TABLE STUDENT_INFO ADD CONSTRAINT BRANCH_CHECK
CHECK (BRANCH IN('CSE','ECE','CE','ME','ELE'));

Output:

Step 9: Display the current constraints applied on the table STUDENT_INFO.

Query:

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='STUDENT_INFO';

Note: Since our table has 2 constraints i.e. the PRIMARY KEY constraint and the CHECK constraint currently, hence 2 rows are displayed.

Output:

Step 10: Add a UNIQUE constraint named UNIQ to the STUDENT_NAME column using ALTER clause.

Query:

ALTER TABLE STUDENT_INFO ADD CONSTRAINT 
UNIQ UNIQUE(STUDENT_NAME);

Output:

Step 11: Display the current constraints applied on the table STUDENT_INFO.

Query:

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='STUDENT_INFO';

Note: Since our table has 3 constraints i.e. the PRIMARY KEY constraint, the CHECK constraint, and the UNIQUE constraint currently, hence 3 rows are displayed.

Output: