Contents
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()
);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;