DEFAULT

DEFAULT constraint is used to set a default value in the column, when no other values are inserted into it. 


To understand about this constraint, let us create a new table customers with their attributes: ID, Name, Phone. We want to set a default value to the column Phone as 0. 

CREATE TABLE customers (

id INT  NOT NULL,

name VARCHAR(255)  NOT NULL,

phone INT  NOT NULL DEFAULT 0,

);

Using, INSERT INTO we can add records to the table. 

INSERT INTO customers(id, name, phone) VALUES (1, "ABC", "1234567890");

INSERT INTO customers(id, name, phone) VALUES (2, "XYZ", "1234567777");

Now, we do not have phone record for customer id 3. We do not want this column to have NULL values, so we provided NOT NULL constraint to it. Let us see what happens if we do not provide any values to that column for customer id = 3.

INSERT INTO customers(id, name) VALUES (3, "ZZZ" );

SELECT * FROM customers;

We can see in the table above, default value '0' is inserted into the column even though we did not specify any value to that column for customer id = 3. 

DEFAULT constraint on table that is already created!

What if the table customers was already created and we wanted to add DEFAULT constraint to phone later? We can do so by using following syntax:

ALTER TABLE customers

ALTER phone SET DEFAULT 0;

Deleting DEFAULT constraint from the table

To remove the DEFAULT constraint from the column we can use following SQL syntax:

ALTER TABLE customers

ALTER phone DROP DEFAULT;