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 w3wiki.
Query:
CREATE DATABASE w3wiki
Output:
Step 2: Use the w3wiki database. For this use the below command.
Query:
USE w3wiki
Output:
Step 3: Create a table STUDENT_INFO inside the database w3wiki. 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: