Code has been added to clipboard!

Using SQL DEFAULT Value Constraint

Reading time 1 min
Published Aug 9, 2017
Updated Oct 11, 2019

SQL DEFAULT Constraint: Main Tips

  • This constraint is used to write a value in a column which is default.
  • When no different values are defined, by default it will be inserted for all new data records.

DEFAULT Constraint on CREATE TABLE

In the code example below we produce the constraint DEFAULT in the table "Person" on the column "CityID":

MS Access / Oracle / My SQL / SQL Server:

Example
CREATE TABLE Person (
    ID int NOT NULL,
    LName varchar(255) NOT NULL,
    FName varchar(255),
    AgeID int,
    CityID varchar(255) DEFAULT 'ben'
);

This constraint is useful when you need to write in a system value. You can do this with a function like GETDATE():

Example
CREATE TABLE Order (
    ID int NOT NULL,
    OrdersNumber int NOT NULL,
    OrdersDate date DEFAULT GETDATE()
);
Udacity
Pros
  • Simplistic design (no unnecessary information)
  • High-quality courses (even the free ones)
  • Variety of features
Main Features
  • Nanodegree programs
  • Suitable for enterprises
  • Paid certificates of completion
Coursera
Pros
  • Professional service
  • Flexible timetables
  • A variety of features to choose from
Main Features
  • Professional certificates of completion
  • University-level courses
  • Multiple Online degree programs
Datacamp
Pros
  • Great user experience
  • Offers quality content
  • Very transparent with their pricing
Main Features
  • Free certificates of completion
  • Focused on data science skills
  • Flexible learning timetable

DEFAULT Constraint on ALTER TABLE

In the code example below we produce the constraint DEFAULT after the table is already created on the column "CityID":

MySQL:

Example
ALTER TABLE Person
ALTER CityID SET DEFAULT 'Ben';

SQL Server / MS Access:

Example
ALTER TABLE Person
ALTER COLUMN CityID SET DEFAULT 'Ben';

Oracle:

Example
ALTER TABLE Person
MODIFY CityID DEFAULT 'Ben';

DROP a DEFAULT Constraint

When dropping the constraint:

MySQL:

Example
ALTER TABLE Person
ALTER CityID DROP DEFAULT

SQL Server / Oracle / MS Access:

Example
ALTER TABLE Person
ALTER COLUMN CityID DROP DEFAULT;

Latest Udacity Coupon Found:

Verified STAFF PICK

75% OFF COURSES

Udacity Black Friday Offer

The best time to save on Udacity courses is now - follow this coupon to access a 75% Udacity Black Friday discount & enjoy learning at a very low cost!

Expiration date: 08/03/2021
3176 People Used
Only 97 Left
Rating
5.0