SQL DEFAULT Constraint

The DEFAULT constraint is used to set a default value for a column.

The default value will be added to all new records, if no other value is specified.

SQL DEFAULT on CREATE TABLE

The following SQL creates a DEFAULT value for the "City" column when the "student" table is created:

Example

My SQL / SQL Server / Oracle / MS Access:
 CREATE TABLE  student(
id int  NOT NULL  ,
Last_name varchar( 50 ) NOT NULL ,
 First_name varchar( 50 ),
Age int,
City varchar( 50 ) DEFAULT 
 'Ranchi' 
);

The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():

Example

 CREATE TABLE  products(
id int  NOT NULL  ,
Product_name varchar( 50 ) NOT NULL ,
 Order_date date  DEFAULT  GETDATE()
 );

SQL DEFAULT on ALTER TABLE

To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:

Example

 SQL Server:
 ALTER TABLE  student
ADD CONSTRAINT  df_City
  DEFAULT 'Ranchi' FOR City ;

Example

 Oracle: 
 ALTER TABLE  student
  MODIFY City DEFAULT 'Ranchi' ;

Example

MS Access:
 ALTER TABLE  student
  ALTER COLUMN City SET DEFAULT 'Ranchi' ;

DROP a DEFAULT Constraint

To drop a DEFAULT constraint, use the following SQL:

Example

MySQL 
 ALTER TABLE  student
 ALTER  City DROP DEFAULT  ;

Example

 SQL Server / Oracle / MS Access:
 ALTER TABLE  student
 ALTER COLUMN  City DROP DEFAULT  ;