SQL Server DEFAULT Constraint

The DEFAULT constraint is used to set the default value of the column as some value so that later it can be updated or if the value of a column cell is not changed the default will remain. If we don’t add the DEFAULT constraint to a column the SQL’s default will be set which is NULL.

Syntax:

CREATE TABLE table_name
(
column1  data_type,
column2 data_type DEFAULT  default_value
)

Usage of DEFAULT Constraint

Step 1: Create database Geeksforgeeks by using the following SQL query.

CREATE DATABASE Geeksforgeeks

Step 2: Use the GFG Database.

USE Geeksforgeeks

Step 3: Create a table with the name studentsMark which contains the studentNames along with their details with gradeObatined as DEFAULT value ‘NA‘.

CREATE TABLE studentsMarks
(
studentId INT PRIMARY KEY,
studentName VARCHAR(100),
courseId VARCHAR(100),
marksObtained INT,
gradeObtained VARCHAR(10) DEFAULT 'NA'
);

Explanation: Here in the query we have declared the ‘gradeObtained‘ column with a default value of ‘NA‘

Step 4: Inserting rows into the table.

INSERT INTO studentsMarks(studentId, studentName , courseId,marksObtained)
VALUES (19104060, 'Student1','ECPC-1001', 25),
(19104061, 'Student2' ,'ECPC-1001', 96),
(19104062, 'Student3', 'ECPC-1001', 81),
(19104063, 'Student4', 'ECPC-1001', 85),
(19104064, 'Student5', 'ECPC-1001', 86),
(19104065, 'Student6', 'ECPC-1001', 55),
(19104066, 'Student7', 'ECPC-1001', 70),
(19104067, 'Student8', 'ECPC-1001', 71),
(19104068, 'Student9', 'ECPC-1001', 65),
(19104069, 'Student10', 'ECPC-1001', 68);

Step 5: Check the table the gradeObtained column will be ‘NA‘ by default.

SELECT * FROM studentsMarks

The Result Looks Like:

studentsMarks Table

How to Update the Default Column of gradeObtained for studentId = 19104060

UPDATE studentsMarks
SET gradeObtained = 'R' 
WHERE studentId = 19104060
SELECT * FROM studentsMarksOutput 

The Result Looks Like:

After Update Our Table Looks

Explanation: In the query we have overridden the current default value of ‘NA‘ to the new value of ‘R’ for studentId = 19104060 using the UPDATE and SET. The gradeObtained column with a default value of ‘NA‘ is updated with ‘R’ for the studentId 19104060

How to Add a New Column gradeLockStatus with Default Constraint as ‘NOT LOCKED’

ALTER TABLE studentsMarks
 ADD gradeLockStatus VARCHAR(50)
CONSTRAINT defaultConstraint DEFAULT 'NOT LOCKED' NOT NULL
SELECT * FROM studentsMarks 

The Result Looks Like:

After adding gradeLockStatus column as NOT LOCKED

Explanation: Using the ALTER command we have added the new column ‘gradeLockStatus‘ with a DEFAULT constraint of default value ‘NOT LOCKED’. A new column gradeLockStatus with a default value of ‘NOT LOCKED’ is added to the table.

How to Override the Current Default Constraint

ALTER TABLE studentsMarks
DROP CONSTRAINT defaultConstraint;

ALTER TABLE studentsMarks
ADD CONSTRAINT defaultConstraint1 DEFAULT 'LOCKED' FOR gradeLockStatus;

NSERT INTO studentsMarks(studentId, studentName , courseId,marksObtained)
VALUES (19104071, 'Student11','ECPC-1001', 25)

SELECT * FROM studentsMarks

The Result Looks Like:

Explanation: In the query, With the first ALTER command we have dropped the DEFAULT constraint by it’s name and using the second alter statement we have added another constraint with another default value and using the INSERT we have added a row to check from now it’s having default value ‘LOCKED‘ or not. The constaint name of the default value set before for the ‘gradeLockStatus‘ is dropped and then new constraint is added with a new default value of ‘LOCKED‘ and a new row is added with ‘studentId‘ 19104071 is added which has a default value of ‘LOCKED’.