1. Introduction to default value constraints

The full name for Default values is Default Constraint. The MySQL default value constraint is used to specify the default value for a column.

For example, if there are many female employees, the gender can be female by default. If this field is not assigned when a new record is inserted, it is automatically assigned female.

Set the default constraint when creating the table

When creating a table, you can use the DEFAULT keyword to set the DEFAULT constraint. The syntax is as follows:

< field name > < data type > DEFAULT < DEFAULT value >;Copy the code

In example 1, create table demo_user and specify sex as female by default and city as Beijing by default.

drop table if exists demo_user;
create table if not exists demo_user(
id int(11) primary key,
sex varchar(3) DEFAULT 'woman',
city varchar(10) DEFAULT 'Beijing'
);

Insert test data
insert into demo_user(id) values(1);
insert into demo_user(id,sex) values(2.'male');
insert into demo_user(id,city) values(3.'Shanghai');
-- Query result
select * from demo_user;
Copy the code

The result is that all sex and city fields, with no data set to insert, are stored with default values.

Add default constraint when modifying table

There are two types of syntax, the CHANGE and MODIFY clauses.

ALTER TABLE< table name >MODIFY COLUMN< field name > < data type >DEFAULT< default value >;Copy the code
ALTER TABLE< table name >CHANGE COLUMN< old field name > < new field name > < data type >DEFAULT< default value >;Copy the code

Example 2, modify the demo_user data table, city city default is Tianjin.

-- Use the CHANGE clause
alter table demo_user CHANGE COLUMN city city varchar(10) DEFAULT 'tianjin';
-- Use the MODIFY clause
alter table demo_user MODIFY COLUMN city varchar(10) DEFAULT 'tianjin';
insert into demo_user(id) values(4);
select * from demo_user;
Copy the code

The result is as follows. The default value changes to Tianjin.

Delete default value constraint

The syntax for deleting default constraints when modifying tables is as follows:

ALTER TABLE< table name >CHANGE COLUMN< field name > < field name > < data type >DEFAULT NULL;
Copy the code

In example 3, modify the demo_user data table and set the default value of the city field to null, which is still possible for non-empty fields.

alter table demo_user MODIFY COLUMN city varchar(10) DEFAULT NULL;
Copy the code