Home » How to use DEFAULT in SQL

How to use DEFAULT in SQL

by Online Tutorials Library

How to use DEFAULT in SQL

In this SQL article, you will learn how to use DEFAULT on the columns of the table in Structured Query Language.

What is a DEFAULT constraint?

The DEFAULT is a constraint in SQL which allows users to fill a column with the default or fixed value.

If no value is specified to the column at the time of insertion, then the default value will be added automatically to that column.

The following syntax adds the DEFAULT constraint to the column at the time of table creation:

In the SQL DEFAULT syntax, we have to define the value with the DEFAULT constraint. The database users can easily specify the DEFAULT constraint to one or more columns in one SQL table.

The following syntax adds the DEFAULT constraint to the column when the table already exists:

If you want to use the DEFAULT constraint at the time of table creation, you have to follow the steps given below:

  1. Create the new database
  2. Create a new table and add DEFAULT
  3. Insert the records
  4. View the table’s data

Step 1: Create the Simple new database

Firstly, you have to make a new database in Structured Query Language.

The following query creates the new Industry Database in SQL server:

Step 2: Create the New table and add DEFAULT

The following query creates the Client_Info table in the Industry Database and adds the CHECK constraint to the Client_Age column of the table:

Step 3: Insert the Values

The following INSERT queries insert the records of clients in the Client_Info table:

Step 4: View the Table’s Data

The following query shows the data of the Client_Info table.

Client_ID Client_Name Client_Gender Client_Age Client_Address
1001 Arush Male 18 Agra
1002 Bulbul Female 18 Lucknow
1004 Saurabh Male 20 Lucknow
1005 Shivani Female 18 Agra
1006 Avinash Male 22 Delhi
1007 Shyam Male 18 Banglore

Add DEFAULT Constraint on Multiple columns

The following CREATE TABLE query specifies the DEFAULT constraint on more than one column to the Doctor_Info table:

The following query inserts the multiple records of doctors in the Doctor_Info table:

The following query shows the details of the Doctor_Info table:

Doctor_ID Doctor_Name Doctor_Disease Doctor_Gender Doctor_Country
1035 Jones Heart Male U. K.
1015 Moris Heart Male Russia
1003 Harry Fever Male U. K.
1044 Bella Heart Female U. K.
1025 Moria Heart Male Russia

Delete DEFAULT constraint from the table

The ALTER COLUMN keyword with ALTER TABLE statement allows the database users to remove the DEFAULT constraint from the column of the table.

The following ALTER syntax is used to remove the DEFAULT constraint from the SQL table,

The following query deletes the DEFAULT value Russia from the Doctor_Country column of the Doctor_Info table:

To check the result of the above ALTER query, you have to type the following DESC command, which describes the structure of the Doctor_Info table:

Output:

Field Type NULL Key Default Extra
Doctor_ID INT NO NULL
Doctor_Name INT NO NULL
Doctor_Specialist Varchar(20) NO Heart
Doctor_Gender Varchar(20) NO Male
Doctor_Country INT Yes NULL

As we can see in the above Doctor_Info table, the value of the DEFAULT column is NULL for the Doctor Country field, which shows that the DEFAULT value is successfully removed from the Doctor_Country column.

Add DEFAULT constraint to Existing table

The database users can easily add DEFAULT value to the column of an existing table by using the ADD keyword in the SQL ALTER TABLE statement.

The following syntax is used in SQL to add the DEFAULT constraint in the existing table:

The following query adds the DEFAULT value as INDIA to the Doctor_Country column of the Doctor_Info table:

To check the result of the above ALTER query, you have to type the following DESC command, which describes the structure of the Doctor_Info table:

Output:

Field Type NULL Key Default Extra
Doctor_ID INT NO NULL
Doctor_Name INT NO NULL
Doctor_Specialist Varchar(20) NO Heart
Doctor_Gender Varchar(20) NO Male
Doctor_Country INT N0 INDIA

You may also like