SQL DEFAULT Constraint
SQL DEFAUULT CONSTRAINT example – Learn how to store default value if no value inserted for columns in the table in MySQL database with example and important points.
POINTS TO NOTICE
- Default constraint allows to set default values for a column.
- The default value will be added to all the records if no other value is specified (when doing insertion).
SQL syntax to define a Default Constraint
We can create a default constraint on a column of a table by using create table statement.
CREATE TABLE [table_name]
(
column1 [column1_definition] default [default_value],
column2 [column2_definition] default [default_value],
…………………………………………………………………….
);
Here is the example query to define default constraint using create table statement.
CREATE TABLE ADDRESSBOOK
(
ADDRBOOK_ID BIGINT ,
MEMBER_ID BIGINT NOT NULL,
DSIPLAYNAME VARCHAR(254) default ‘JAVA’,
constraint ADDRESSBOOK_pk primary key(ADDRBOOK_ID,MEMBER_ID)
);
In the above example for display name column default value is ‘java’ if data insertion is not done.
SQL syntax to define default constraint using alter table statement.
ALTER TABLE [table_name]
ALTER [Column_name] set default [default_value];
here is the example query to define default constraint using alter statement.
ALTER TABLE ADDRESSBOOK
ALTER DSIPLAYNAME SET DEFAULT ‘JAVA’;
SQL syntax to Drop a Default Constraint
Syntax:
ALTER TABLE [table_name]
ALTER [COLUMN_NAME] DROP DEFAULT;
here is the example query to drop a default constraint.
Query example:
ALTER TABLE ADDRESSBOOK
ALTER DSIPLAYNAME DROP DEFAULT;