1. SQL Tutorial
  2. SQL DEFAULT Constraint

SQL DEFAULT Constraint

Submitted by admin on Monday, April 25, 2011 - 11:11.

The DEFAULT Constraint is used to insert the default values into a column during design time.

The default values is used in new record if no value is specified during INSERT Statement.

The default

SQL DEFAULT Constraint Syntax

  1. CREATE TABLE TABLE_NAME
  2. (
  3.   column_name1 data_type DEFAULT VALUE,
  4.   column_name2 data_type DEFAULT VALUE,
  5.   column_name3 data_type DEFAULT VALUE,
  6.   ....
  7. )

Example

  1. CREATE TABLE Users
  2. (
  3.   ID text NOT NULL,
  4.   Firstname text,
  5.   Lastname text,
  6.   Salary FLOAT,
  7.   DeptID INT  NOT NULL,
  8.   DateAdded DATE DEFAULT GetDate()
  9. )

Users

ID Firstname Lastname Salary DeptID DateAdded

This will set the default value into DateAdded field if no value is passed using the INSERT Statement and will give the default system date.

ALTER TABLE method

Example MySQL

  1. ALTER TABLE Users
  2. ALTER DateAdded SET DEFAULT GetDate()

Example Oracle/SQL server/MS Access

  1. ALTER TABLE Users
  2. ALTER COLUMN DateAdded SET DEFAULT GetDate()

TO REMOVE DEFAULT CONSTRAINT

Example MySQL

  1. ALTER TABLE Users
  2. ALTER DateAdded DROP DEFAULT

Example Oracle/SQL Server/MS Access

  1. ALTER TABLE Users
  2. ALTER COLUMN DateAdded DROP DEFAULT
Tags
sql tutorial
DEFAULT Constraint

Add new comment

About text formats
  • 97 views