90 SQL - 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 −

CREATE TABLE 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 −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL DEFAULT 'Ramesh',
   AGE INT NOT NULL DEFAULT '32',
   ADDRESS CHAR (25) DEFAULT 'Ahmedabad',
   SALARY DECIMAL (18, 2) DEFAULT '2000',
   PRIMARY KEY (ID)
);

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

INSERT INTO CUSTOMERS (ID) VALUES (1);
INSERT INTO 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 −

INSERT INTO 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 −

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT 'default_value';

Example

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

CREATE TABLE BUYERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

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

ALTER TABLE BUYERS ALTER ADDRESS SET DEFAULT 'Delhi';

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

INSERT INTO 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 −

ALTER TABLE table_name
ALTER COLUMN column_name DROP DEFAULT;

Example

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

ALTER TABLE CUSTOMERS ALTER ADDRESS DROP DEFAULT;

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

Print Page