SQL – DEFAULT

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)
VALUES (‘Surendren’,  ‘ ’, 96);

RUN SQL


SQL queries output:

sql-table65

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;

Prev     Next

Share this website !!!
Facebook Twitter Linkedin