SQL DEFAULT Constraint : add Default Value Into a Column
The DEFAULT constraint is used to set a default value for a column. In the below example City column has the default value Sandnes
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT ‘Sandnes’
);
The default value will be added to all new records if no other value is specified.
Example of default constraint
Example –
To set a DEFAULT value for the “Location” column when the “edu” table is created –
CREATE TABLE edu( ID int NOT NULL, Name varchar(255), Age int, Location varchar(255) DEFAULT 'Delhi');
SQL DEFAULT on CREATE TABLE
The following SQL sets a DEFAULT value for the “City” column when the “Persons” table is created:
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 a constraint can also be used to insert system values, by using functions like GETDATE(): CREATE TABLE Orders (
ID int NOT NULL,
OrderNumber int NOT NULL,
OrderDate date DEFAULT GETDATE()
);
Few more example of SQL Default constraint:
Example
For example, the following SQL creates a new table called CUSTOMERS and adds five columns. Here, the SALARY column is set to 5000.00 by default, so in case the INSERT INTO statement does not provide a value for this column, then by default this column would be set to 5000.00.
CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2) DEFAULT 5000.00, PRIMARY KEY (ID) );
If the CUSTOMERS table has already been created, then to add a DEFAULT constraint to the SALARY column, you would write a query like the one which is shown in the code block below.
ALTER TABLE CUSTOMERS
MODIFY SALARY DECIMAL (18, 2) DEFAULT 5000.00;
SQL DEFAULT on ALTER TABLE
To create a DEFAULT constraint on the “City” column when the table is already created, use the following SQL:
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’;
DROP a DEFAULT Constraint
To drop a DEFAULT constraint, use the following SQL:
MySQL: ALTER TABLE Persons
ALTER City DROP DEFAULT;
SQL Server / Oracle / MS Access:ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;
