SQL DEFAULT Constraint

The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified. You can also specify the DEFAULT value for columns i.e. when user does not enter anything in that column then that column will have the default value. Default constraints are ANSI -standard constraints you can assign to a column either with the CREATE TABLE or ALTER TABLE statements.

Example: in Persons table suppose most of the Persons are from Ferozepur, then you can put this as default value for CITY column. Then while inserting records if user doesn’t enter anything in the CITY column then the city column will have Ferozepur.

Syntax: <column_name><datatype> [default <expression>]

Query:

SQL> CREATE TABLE Persons(P_Id number (6) NOT NULL CHECK (P_Id>0),LastName varchar2 (15) NOT NULL,FirstName varchar2 (15),Address varchar2 (25),City varchar (15) DEFAULT ‘Ferozepur’);

Table created.
 
Now, when user inserts record like this

SQL> insert into Persons values (301,'Kumar' ,'Vinod' ,'VPO Rukna MungIa',' Jalandhar');
 
Then the city column will have value 'Jalandhar '. But when user inserts a record like this.
SQL> insert into Persons values (302,'Dhillon','Raman','Preet nagar');
 
Then the city column will have value 'Ferozepur'. Since it is the default.