SQL DEFAULT Constraint

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

  1. Default constraint allows to set default values for a column.
  2. 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;

Related Posts