Default Constraint

The SQL DEFAULT Constraint

The SQL DEFAULT Constraint is used to specify the default value for a column of a table. We usually set default value while creating the table.

The default values are treated as the column values if no values are provided while inserting the data, ensuring that the column will always have a value. We can specify default values for multiple columns in an SQL table.

Syntax

Following is the syntax of the SQL DEFAULT Constraint −

CREATETABLE table_name (
   column1 datatype DEFAULT default_value,
   column2 datatype DEFAULT default_value,
   column3 datatype,.....
   columnN datatype
);

Example

In the following query we are creating the CUSTOMERS table using the CREATE TABLE statement. Here, we are adding a default constraint to the columns NAME, AGE, ADDRESS, and SALARY −

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULLDEFAULT'Ramesh',
   AGE INTNOTNULLDEFAULT'32',
   ADDRESS CHAR(25)DEFAULT'Ahmedabad',
   SALARY DECIMAL(18,2)DEFAULT'2000',PRIMARYKEY(ID));

Following query inserts values into this table using the INSERT statement −

INSERTINTO CUSTOMERS (ID)VALUES(1);INSERTINTO CUSTOMERS VALUES(2,'Khilan',25,'Delhi',1500.00);

The table is created with default values in the NAME, AGE, ADDRESS, and SALARY columns for the first row as shown below −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00

Passing “DEFAULT” as Value

While inserting data into a table, if the column names are not included in the INSERT query, to insert the default value into the record we need to pass “DEFAULT” as a value, as shown below −

INSERTINTO CUSTOMERS VALUES(3,'Kaushik',DEFAULT,DEFAULT,2000.00),(4,'Chaitali',DEFAULT,DEFAULT,DEFAULT);

The table obtained is as shown below −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	32	Ahmedabad	2000.00
4	Chaitali	32	Ahmedabad	2000.00

Adding Default Constraint to an Existing Column

We can also add default constraints to an existing column of a table using the ALTER TABLE statement. This allows us to modify the structure of existing table by specifying default values, ensuring data consistency in the database.

Syntax

Following is the syntax for adding a default constraint to a column in an existing table −

ALTERTABLE table_name
ALTERCOLUMN column_name SETDEFAULT'default_value';

Example

Assume we have created another table named BUYERS using the CREATE TABLE statement as shown below −

CREATETABLE BUYERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

Following query, we adds a default constraint to the ADDRESS column of the BUYERS table.

ALTERTABLE BUYERS ALTER ADDRESS SETDEFAULT'Delhi';

Following INSERT statement inserts a record into the BUYERS table by providing values to all the columns except ADDRESS −

INSERTINTO BUYERS (ID, NAME, AGE, SALARY)VALUES(01,'Rahul',27,50000);

Verification

After inserting the record if you retrieve it back, you can observe the default value (“Delhi”) in the address column −

SELECT*FROM BUYERS WHERE ID =01;

Output

The table obtained is as follows −

ID	NAME	AGE	ADDRESS	SALARY
01	Rahul	27	Delhi	50000.00

Dropping Default Constraint

We can delete the default constraint from a table using the ALTER TABLE… DROP statement.

Syntax

Following is the syntax to delete the default constraint from a table −

ALTERTABLE table_name
ALTERCOLUMN column_name DROPDEFAULT;

Example

In here, we are removing the default constraint from the ADDRESS column of the CUSTOMERS table −

ALTERTABLE CUSTOMERS ALTER ADDRESS DROPDEFAULT;

Verification

We can verify the table details (structure) and check whether there is a default constraint or not using the following query −

DESC CUSTOMERS;

The table obtained is as shown below −

Field	Type	Null	Key	Default	Extra
ID	int	NO	PRI	NULL
NAME	varchar(20)	NO		Ramesh
AGE	int	NO		32
ADDRESS	char(25)	YES		NULL
SALARY	decimal(18,2)	YES		2000.00