🚨 Get Your Free NFT Certificate Mint by Completing the Web3 Exam! START NOW

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()
);
DataCamp
Pros
  • Easy to use with a learn-by-doing approach
  • Offers quality content
  • Gamified in-browser coding experience
  • The price matches the quality
  • Suitable for learners ranging from beginner to advanced
Main Features
  • Free certificates of completion
  • Focused on data science skills
  • Flexible learning timetable
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
Udemy
Pros
  • Easy to navigate
  • No technical issues
  • Seems to care about its users
Main Features
  • Huge variety of courses
  • 30-day refund policy
  • Free certificates of completion

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;