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 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 INFO inside the database w3wiki. 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.
Contact Us