The DEFAULT constraint sets a predefined value for a column.
This value will be assigned to all new records in the absence of any other specified value.
This SQL statement assigns a DEFAULT value to the “City” column during the creation of the “Persons” table.
My SQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255) DEFAULT ‘Sandnes’ ); |
The DEFAULT constraint can also insert system values, achieved by utilizing functions such as GETDATE().
CREATE TABLE Orders ( ID int NOT NULL, OrderNumber int NOT NULL, OrderDate date DEFAULT GETDATE() ); |
If you need to establish a DEFAULT constraint on the “City” column after the table has already been created, utilize the following SQL statement:
MySQL:
ALTER TABLE Persons ALTER City SET DEFAULT ‘Sandnes’; |
SQL Server:
ALTER TABLE Persons ADD CONSTRAINT df_City DEFAULT ‘Sandnes’ FOR City; |
MS Access:
ALTER TABLE Persons ALTER COLUMN City SET DEFAULT ‘Sandnes’; |
Oracle:
ALTER TABLE Persons MODIFY City DEFAULT ‘Sandnes’; |
To remove a DEFAULT constraint, utilize the following SQL statement:
MySQL:
ALTER TABLE Persons ALTER City DROP DEFAULT; |
SQL Server / Oracle / MS Access:
ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT; |
SQL Server:
ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT; |