How to Use SQL Query to Rename a Constraint?

In SQL, we sometimes need to rename the constraints of 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 INFO inside the database GeeksForGeeks. This table has 3 columns namely S_NAME, ROLL, and BRANCH containing the name, roll number, and branch of various students studying in an engineering college.

Query:

CREATE TABLE INFO(
S_NAME VARCHAR(20),
ROLL INT,
BRANCH VARCHAR(5));

Output:

Step 4: Add a user-defined CHECK CONSTRAINT named BRANCH_CHECK constraint to the INFO table. This constraint checks that the branch of a student entered in the database is among the 5 branches taught in the engineering college namely Computer Science and Engineering, Electronics and Communication Engineering, Civil Engineering, Mechanical Engineering, and Electrical Engineering.

Query:

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

Output:

Step 5: Describe the structure of the table INFO.

Query:

EXEC SP_COLUMNS INFO;

Output:

Step 6: Insert 5 rows into the INFO table such that the branches are within the 5 branches defined in the BRANCH_CHECK constraint. So no error is thrown while adding these rows.

Query:

INSERT INTO INFO VALUES('MATT',1001,'CSE');
INSERT INTO INFO VALUES('SAM',1002,'ECE');
INSERT INTO INFO VALUES('NICK',1003,'CE');
INSERT INTO INFO VALUES('JOHN',1004,'ELE');
INSERT INTO INFO VALUES('BRUCE',1005,'ME');

Output:

Step 7: Insert rows into the INFO table such that the branch is NOT within the 5 branches defined in the BRANCH_CHECK constraint. This throws an error as it is violating the constraint.

Query:

INSERT INTO INFO VALUES('MORRIS',1006,'MECH');

Output:

Step 8: Display all the rows of the INFO table.

Query:

SELECT * FROM INFO;

Output:

Step 9: Display all the constraints defined for the INFO table.

Query:

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

Output:

Step 10: Rename the user-defined check constraint from BRANCH_CHECK to CHECK_BRANCH.

Query:

SP_RENAME 'BRANCH_CHECK','CHECK_BRANCH';

Output:

Step 11: Display all the constraints defined for the INFO table. The new name i.e. CHECK_BRANCH must be visible now instead of the old name i.e. BRANCH_CHECK.

Query:

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

Output:

Thus, in an above-stated manner, one can rename any constraint present in the table.