The DEFAULT constraint provides a default value to a column when there is no value provided while inserting a record into a table. Lets see how to specify this constraint and how it works.
Specify DEFAULT constraint while creating a table
Here we are creating a table “STUDENTS”, we have a requirement to set the exam fees to 10000 if fees is not specified while inserting a record (row) into the STUDENTS table. We can do so by using DEFAULT constraint. As you can see we have set the default value of EXAM_FEE column to 10000 using DEFAULT constraint.
CREATE TABLE STUDENTS( ROLL_NO INT NOT NULL, STU_NAME VARCHAR (35) NOT NULL, STU_AGE INT NOT NULL, EXAM_FEE INT DEFAULT 10000, STU_ADDRESS VARCHAR (35) , PRIMARY KEY (ROLL_NO) );
Specify DEFAULT constraint while creating a table
What if we want to set this constraint on a already existing table. For this we can ALTER Table statement like this:
Syntax:
ALTER TABLE <table_name> MODIFY <column_name> <column_data_type> DEFAULT <default_value>;
Example:
ALTER TABLE STUDENTS MODIFY EXAM_FEE INT DEFAULT 10000;
This way we can set constraint on a already created table.
How to drop DEFAULT Constraint
In the above sections, we have learnt the ways to set Constraint. Here we will see how to drop (delete) a Constraint:
Syntax:
ALTER TABLE <table_name> ALTER COLUMN <column_name> DROP DEFAULT;
Example:
Lets say we want to drop the constraint from STUDENTS table, which we have created in the above sections. We can do it like this.
ALTER TABLE CUSTOMERS ALTER COLUMN EXAM_FEE DROP DEFAULT;
Leave a Reply