WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL DEFAULT Constraint


SQL DEFAULT constraint

DEFAULT constraints are used to insert default values into columns.

If no other value is specified, the default value is added to all new records.


SQL DEFAULT constraint when CREATE TABLE

The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created:

My SQL /SQL Server /Oracle /MS Access:

CREATE TABLE Persons
(
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) DEFAULT 'Sandnes'
)

DEFAULT constraints can also be used to insert system values by using functions like GETDATE ():

CREATE TABLE Orders
(
    O_Id int NOT NULL,
    OrderNo int NOT NULL,
    P_Id int,
    OrderDate date DEFAULT GETDATE()
)


SQL DEFAULT constraint when ALTER TABLE

When the table has been created, to create a DEFAULT constraint in the "City" column, use the following SQL:

MySQL:

ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

SQL Server /MS Access:

ALTER TABLE Persons
ADD CONSTRAINT ab_c DEFAULT 'SANDNES' for City

Oracle:

ALTER TABLE Persons
MODIFY City DEFAULT 'SANDNES'


Revoke DEFAULT constraint

To remove the 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