Prev Next
- DEFAULT is a constraint in SQL which is used to insert the default value for a column while inserting new records in a table.
- This default value will be inserted only when there is no other value specified in insert query in the respective column/field. Syntax for SQL DEFAULT constraint is given below.
SQL Syntax for DEFAULT constraint:
| SQL Syntax for DEFAULT constraint | CREATE TABLE table_name (column_name1 INTEGER AUTOINCREMENT, column_name2 data_type(size), column_name3 data_type(size) DEFAULT value, etc…); |
Example for how to use SQL DEFAULT constraint:
- Please execute below query in SQL to create a table with DEFAULT constraint.
| CREATE TABLE student (Student_ID int AUTOINCREMENT, Student_name varchar(255), City varchar(255) DEFAULT ‘Chennai’, Marks int); |
- Now, a new table called “student” is created and the field “City” has a default vale ‘Chennai’. So whenever a new record is inserted, the column ‘City’ is populated with Chennai if this field value is not specified by insert query.
- Now, please insert some records using insert SQL as below and see how data are inserted into this newly created table for the field ‘City’.
SQL queries:
| INSERT INTO student (Student_name, Marks) VALUES (‘Raju’, 80);INSERT INTO student (Student_name, Marks) VALUES (‘Mani’, 85);INSERT INTO student (Student_name, City, Marks) VALUES (‘Thiyagarajan’, ‘Vizag’, 70); INSERT INTO student (Student_name, City, Marks) |
SQL queries output:
How to ALTER a DEFAULT constraint?
- To ALTER a DEFAULT value of an existing table, you can use below Syntax.
Syntax:
| ALTER table table_name ALTER column_name SET DEFAULT value; |
Example:
| ALTER table Student ALTER City SET DEFAULT ‘Delhi’; |
How to DROP a DEFAULT constraint?
- To DROP a DEFAULT constraint of an existing table, you can use below Syntax.
Syntax:
| ALTER table table_name ALTER column_name DROP DEFAULT; |
Example:
| ALTER table Student ALTER City SET DROP DEFAULT; |