SQL - Default Constraint

---

In general, a default constraint is useful when the value has not been passed in the column that is specified with a default constraint. Then the column data will automatically be filled with the default value.

SQL - Default Constraint

The constraint that allows the user to fill the column with the fixed or default value is known as the Default.

Consider the following scenario − we have created a table with a few columns, without inserting any values into it. Then, by default, default values take over and add default data.

In the SQL table we can pass more than one default value of the Column. We have to define the value with the Default constraint.

To add the default constraint on the column while creating the table, we have to follow these steps.

• Create a new table and add the default constraint.

• Insert the records in the table

• View the table data with the help SQL query

Syntax

Following is the syntax of the default constraint −

CREATE TABLE table_Name(
   Column1 datatype(size) DEFAULT value,
   Column2 datatype(size) DEFAULT value,
   …,
   ColumnN datatype(size) DEFAULT value
); 

Example

In the following SQL query, we are creating a table named emp_Info. While creating the table, we are adding a default constraint to the column.

CREATE TABLE emp_Info(
   E_ID INT NOT NULL,
   NAME VARCHAR(20) DEFAULT 'AmanKr', 
   AGE INT DEFAULT 21, 
   COURSE VARCHAR(20) DEFAULT 'CSE'
);

Inserting the record into the created table named emp_Info.

INSERT INTO emp_Info(E_ID) VALUES(101);
INSERT INTO emp_Info(E_ID, NAME, AGE, COURSE) VALUES(102, 'SonuKr', 23, 'MECH');

View the table data using the following SQL query

SELECT * FROM emp_Info;

When we execute the above SQL query, we get the following table, where in the first row the name, age, and course are fetched by default.

+------+--------+------+--------+
| E_ID | NAME   | AGE  | COURSE |
+------+--------+------+--------+
|  101 | AmanKr |   21 | CSE    |
|  102 | SonuKr |   23 | Mech   |
+------+--------+------+--------+

Note − If the column name is not included in the SQL query while inserting the data, we can still retrieve the default value from the created table by including the DEFAULT keyword in place of the value.

Following is the SQL query to insert the default value −

INSERT INTO emp_Info VALUES(103, DEFAULT, DEFAULT, 'BIO');
INSERT INTO emp_Info VALUES(104, 'VivekKr', DEFAULT, DEFAULT);

View the table data using the following SQL query

SELECT * FROM emp_Info;

When we execute the above SQL query, we get the following table − After inserting the two row's values, which are either the defaults or manually given by the user.

+------+---------+------+--------+
| E_ID | NAME    | AGE  | COURSE |
+------+---------+------+--------+
|  101 | AmanKr  |   21 | CSE    |
|  102 | SonuKr  |   23 | Mech   |
|  103 | AmanKr  |   21 | BIO    |
|  104 | VivekKr |   21 | CSE    |
+------+---------+------+--------+

Default Constraint in an Existing Table

The Default Constraint can also be added to an existing table in a database; not just while creating a new table.

Syntax

Following is the SQL query to add a default constraint to an existing table −

ALTER TABLE table_name ADD CONSTRAINT Default_name DEFAULT default_value FOR Column_name;

Example

The following SQL statement adds a default constraint to an existing table. when there is no default constraint at all. Thus, by using the following query, we are adding a default constraint.

ALTER TABLE customers ADD CONSTRAINT DF_ADDRESS DEFAULT 'NOIDA' FOR ADDRESS;

Following is the insert query to insert the default constraint into the exiting table.

INSERT INTO customers(ID, NAME, AGE, SALARY) VALUES(05,'AMAN', 23, 500000);

Verification

After the insertion of the column's default value, we are displaying the row that was inserted into the table. using the following query (SELECT * FROM customers;) −

+----+------+-----+---------+---------+
| ID | NAME | AGE | ADDRESS | SALARY  |
+----+------+-----+---------+---------+
| 5  | AMAN | 23  | NOIDA   |500000.00|
+----+------+-----+---------+---------+

Drop Default Constraint

To drop the default constraint from the columns of the table, we have to use the ALTER TABLE… DROP statement. This allows the database users to delete the default constraint from the table.

Syntax

Following is the syntax to drop the default constraint −

ALTER TABLE table_name DROP Default_constraint_name;

Example

In the following example, we will see how to drop the default constraint from a table.

But before dropping the constraint, we must verify whether there is a default constraint in the customer's table or not.

Here in the following table, we can see that there is a default constraint on the age column.

+-------------------------+---------------------------------+---------------+-----------------+
| constraint_type         | constraint_name                 | delete_action | constraint_keys |
+-------------------------+---------------------------------+---------------+-----------------+
| DEFAULT on column AGE   | DF_AGE                          |   (n/a)       | ((22))          |
| PRIMARY KEY (clustered))| PK__CUSTOMER__3214EC270E48CD10  | (n/a)         |    ID           |
+-------------------------+---------------------------------+---------------+-----------------+

We are trying to drop the default constraint from the age column of the customers table, using the following SQL query −

ALTER TABLE customers DROP DF_AGE;

Verification

After the execution of the above SQL query, we're verifying the table details to see if there is a default constraint. Using the SQL query (EXEC sp_help 'dbo.customers');).

+-------------------------+---------------------------------+---------------+-----------------+
| constraint_type         | constraint_name                 | delete_action | constraint_keys |
+-------------------------+---------------------------------+---------------+-----------------+
| PRIMARY KEY (clustered))| PK__CUSTOMER__3214EC270E48CD10  | (n/a)         |    ID           |
+-------------------------+---------------------------------+---------------+-----------------+