SQL Defaut

The DEFAULT constraint in SQL is used to provide a default value for a column when no value is specified during INSERT. It helps ensure consistent data and avoids NULL where appropriate.

SQL Default Syntax

CREATE TABLE table_name (
  column_name datatype DEFAULT default_value
);

CREATE TABLE table_name (

  column_name datatype DEFAULT default_value

);

SQL Default Example

SQL Default on Create Table

This query sets a default gender of ‘F’ in the PatientsWithGenderDefault table:

CREATE TABLE PatientsWithGenderDefault (
  patient_id INTEGER PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  dob DATE NOT NULL,
  gender CHAR(1) DEFAULT 'F',
  contact_number VARCHAR(15)
);

CREATE TABLE PatientsWithGenderDefault (

  patient_id INTEGER PRIMARY KEY,

  first_name VARCHAR(50) NOT NULL,

  last_name VARCHAR(50) NOT NULL,

  dob DATE NOT NULL,

  gender CHAR(1) DEFAULT 'F',

  contact_number VARCHAR(15)

);

Now inserting a row without specifying gender will default it to ‘F’.

SQL Default on Alter Table

SQLite supports adding new columns with default values via ALTER TABLE, but only when the new column is added.

This query demonstrates adding a new column with a default value, :

ALTER TABLE Patients
ADD COLUMN nationality TEXT DEFAULT 'Unknown';

ALTER TABLE Patients

ADD COLUMN nationality TEXT DEFAULT 'Unknown';

This adds a nationality column to the existing Patients table and sets its default value to ‘Unknown’.

SQL Default Drop a Default Constraint

SQLite does not support dropping a default constraint directly.

If you need to change or remove a default:

• Create a new table without the DEFAULT
• Copy data over
• Drop the old table
• Rename the new table

SQL Default Labs