A constraint is actually a constraint on the data in a table.

Function: the purpose of adding constraints is to ensure the integrity and validity of the records in the table, for example, the values of the column (mobile phone number) of the user table can not be empty, and the values of some columns (ID number) can not be repeated.

Classification:

primary key constraint

Self growth constraint (auto_increment)

Non NULL constraint (not null)

Uniqueness constraint

default constraint

Zero fill constraint

foreign key constraint FK

Primary key constraint

Concept:

MySQL primary key constraint is a column or a combination of multiple columns. Its value can uniquely identify each row in the table, which is convenient to find a row in RDBMS as soon as possible.

The primary key constraint is equivalent to the combination of unique constraint and non empty constraint. The primary key constraint column cannot be duplicated or null value is not allowed.

Only one primary key is allowed for each table.

The key of the primary key constraint is: primary key

When you create a constraint on the primary key, the system will establish a corresponding unique index on the column and column combination by default.

Operation:

Add single column primary key

Method 1: specify the primary key while defining the field

In the create table statement, use the PRIMARY KEY keyword to specify the PRIMARY KEY

create table table name(

...

< field name > < data type > primary key

...

)

create table emp1(
eid int primary key,
name varchar(20),
deptId int,
salary double
)

Method 2: specify the primary key after defining the field

create table table name(

...

[constraint constraint name] primary key [field name]

)

create table emp2(
eid int,
name varchar(20),
deptId int,
salary double,
constraint pk1 primary key(eid)
)

Function of primary key: the columns constrained by primary key are non empty and unique

Add multi column federated primary key

The so-called joint primary key means that the primary key is composed of multiple fields in a table.

be careful:

        1. When the primary key is composed of multiple fields, you cannot declare the primary key constraint directly after the field name.

        2. A table can only have one primary key, and the union primary key is also a primary key.

Syntax:

create table table name(

...

primary key (field 1, field 2,..., field n)

)

create table emp3(
name varchar(20),
deptId int,
salary double,
constraint pk2 primary key(name ,deptId)
)

Note: as long as the columns of the joint primary key are not exactly the same; Each column of the federated primary key cannot be empty

Adding a primary key by modifying the table structure

Primary key constraints can be created not only when the table is created, but also when the table is modified.

Syntax:

create table table name(

...

);

alter table name: add primary key (field list);

-- Add single column primary key
create table emp4(
eid int,
name varchar(20),
deptId int,
salary double
)
alter table emp4 add primary key (eid);

 

-- Add multi column primary key
create table emp5(
eid int,
name varchar(20),
deptId int,
salary double
)
alter table emp5 add primary key(name,deptId);

Delete primary key

When a primary key constraint is not required in a table, it needs to be deleted from the table. Deleting a primary key constraint is much easier than creating a primary key constraint.

Syntax:

alter table data table name drop primary key;

alter table emp1 drop primary key;

Self growth constraint auto_increment

Concept: in MySQL, when the primary key is defined as self growth, the value of this primary key no longer needs the user to input data, but is automatically assigned by the database according to the definition. Each time a record is added, the primary key will automatically increase in the same step size.

By adding Auto to the field_ The increment attribute is used to realize the self growth of the primary key

Syntax:

Field name data type auto_increment

create table t_user1(
id int primary key auto_increment,
name varchar(20)
)

characteristic:

By default, auto_ The initial value of increment is 1. For each new piece of data, the field value will be automatically increased by 1

Only one field in a table can use auto_increment constraint, and the field must have a unique index to avoid sequence number duplication (that is, it is a primary key or part of a primary key).

auto_ The field of the increment constraint must have a NOT NULL attribute.

auto_ The field of increment constraint can only be of integer type (TINYINT, SMALLINT, INT, BIGINT, etc.).

auto_ The maximum value of the increment constraint field is constrained by the data type of the field. If the upper limit is reached, auto_increment will fail.

Specifies the initial value of the auto increment field

If the first record is set with the initial value of this field, the newly added record will automatically increase from this initial value.

Method 1: specify when creating a table

create table t_user2(
id int primary key auto_increment,
name varchar(20)
)auto_increment=100;

Method 2: specify after creating the table

create table t_user3(
id int primary key auto_increment,
name varchar(20)
)
alter table t_user3 auto_increment=200;

Changes of delete and truncate in deleting auto increment columns

Automatically grow after deleting data, starting from breakpoint

truncate data is automatically increased from the default starting value

Non NULL constraint (not null)

The value of MySQL not null constraint value field cannot be empty. For fields with non null constraints, if the user does not specify a value when adding data, the database system will report an error.

Syntax:

Method 1: when creating a table, specify the} field name and the data type is not null;

create table t_user6(
id int,
name varchar(20) not null,
address varchar(20)
)

Method 2: after creating a table, specify alter table table name modify field type not null;

create table t_user7(
id int,
name varchar(20),
address varchar(20)
)
alter table t_user7 modify name varchar(20) not null;

Delete non empty constraints

alter table t_user7 modify name varchar(20);

unique constraint

Unique Key means that the values of fields in all records cannot be repeated. For example, after adding a uniqueness constraint to the id field, the id value of each record is unique and cannot be repeated.

Syntax:

Method 1: field name data type unique

create table t_user8(
id int,
name varchar(20),
phone_number varchar(20) unique
);
insert into t_user8 values(1001,'Zhang San',138);
insert into t_user8 values(1002,'Zhang San 2',null);    -- stay MySQL in null It is different from any value, even from itself

Method 2: alter table name add constraint constraint name unique (column);

create table t_user9(
id int,
name varchar(20),
phone_number varchar(20)
);
alter table t_user9 add constraint unique_pn unique(phone_number);

Delete unique constraint

Format: alter table name drop index unique constraint name;

alter table t_user8 drop index phone_number;    -- If there is no constraint name, use the column name
alter table t_user9 drop index unique_pn;

default constraint

MySQL default value constraint is used to specify the default value of a column.

Syntax:

Method 1: default value of field name data type;

create table t_user10(
id int,
name varchar(20),
address varchar(20) default 'Beijing'	-- Specify default constraints
);

Method 2: alter table name modify column name type default default;

create table t_user11(
id int,
name varchar(20),
address varchar(20)
);
alter table t_user11 modify address varchar(20) default 'Shenzhen';

Zero fill constraint

1. When inserting data, when the length of the value of the field is less than the defined length, the corresponding 0 will be added in front of the value

2. zerofill defaults to int(10)

3. When zerofill is used, the unsigned attribute will be added automatically by default. After using the unsigned attribute, the value range is twice the original value, for example, signed bits - 128 ~ + 127 and unsigned bits 0 ~ 256.

create table t_user12(
id int zerofill,	-- Zero fill constraint
name varchar(20)
);
insert into t_user12 values(123,'Zhang San');
insert into t_user12 values(1,'Li Si');

Delete constraint

alter table t_user12 modify id int;