The usage and difference between indexes and constraints in MySql

1. What is an index?

Index is a data structure that helps MySQL obtain data efficiently.

In addition to saving data, the database also maintains data structures that satisfy specific search algorithms. These data structures reference (point to) the data in some way, so that advanced search algorithms can be implemented on these data structures. This data structure is an index. Indexes can greatly improve MySQL's retrieval speed.

In MySQL, for a Primary Key column, MySQL has automatically established Unique and Index for it.

a. Create index:

create table 表名(
    id int not null,
    name varchar(10) not null,
    index(name(length))      //使用name字段作为索引
);

b. Display index:

show index from 表名;

c. Delete index:

alter table 表名 drop index name;

2. What are constraints?

Constraints are to ensure the integrity and consistency of data. According to the number of fields in the constraint, constraints are divided into column-level constraints and table-level constraints.

Column level constraints: used for a certain field.

Table-level constraints: Used for two or more fields.

##defaultDefault constraintforeign keyForeign key constraint

Constraint type	Definition
not null	Non-null constraint
primary key	Primary key constraint
unique key	Unique constraint

1), unique (upique) constraint

unique constraint uniquely identifies each record in the database table.

Unique and primary key constraints both provide uniqueness guarantees for columns.

The primary key is automatically defined as a unique constraint.

Note: Each table can have multiple unique constraints, but each table can only have one primary key constraint.

//第一种方式
create table persons(
  id_p int not null,
  address varchar(255),
  city varchar(255),
  phone varchar(11) unique    //定义字段的同时，定义约束  
);

//第二种方式
create table persons(
    id_p int not null,
    address varchar(255),
    city varchar(255),
    phone varchar(11),
    unique phone(phone)      //单独一行命令，定义约束
);

//第三种方式
alter table persons add unique city(city);  //表后添加(修改表)

2), default constraint

is used to constrain the default value of the value in the corresponding column (unless the default is empty , otherwise null values ​​cannot be inserted).

create table persons(
    id tinyint primary key auto_increment,
    name varchar(30),
    sex enum('m','w')default 'm'   //定义sex默认值为'm'
);

3), primary key constraints

Each data table can only have one primary key. The primary key ensures the uniqueness of the record, and the primary key automatically Is not null (also serves as the index of the table).

Add a primary key to a table without a primary key

alter table 表名 add primary key (字段名);

4), foreign key (foreign key) constraints

Foreign key Constraints are used to maintain data consistency and integrity, and to implement one-to-one or many-to-many relationships.

• For child tables (tables with foreign key columns) and parent tables (tables referenced by child tables), the storage engine can only be innoDB.

• Foreign key columns and reference columns must have similar data types.
  If it is a numeric type, the length of the number must be the same whether it has the sign bit.
  The length of the character type can be different

• Foreign key columns and references The column must be indexed (MySQL will automatically create an index if the foreign key column does not exist).

//先创建父表，子表才能建外键，父表和子表必须都是innodb引擎。

//city父表
create table city(
    id tinyint primary key,
    name varchar(10) not null
)engine=INNODB;

//students子表
create table students(
  id tinyint primary key auto increment,
  //定义字段时同时定义
  city tinyint,  //外键字段类型要与主表相同
  foreign key(city) references city(id),  //city字段作为外键，引用city表中的id
  )engine=INNODB;
  
  //主表的数据可以修改，但不能删除
  //删除city中的记录
  delete from city where id=1;
  
  //创建外键以后，再删除city记录，就会报错
  ERROR 1451(23000):Cannot delete or update a parent row:
  a foreign key constraint fails(`hxsd`.`students`,
  CONSTRAINT `students ibfk 1` FOREIGN KEY(`city`) REFERENCES `city`(`id`)

3. Delete constraints

a. Delete primary key

alter table 表名 drop primary key;

b. Delete index

alter table 表名 drop index index_name;

c. Delete foreign key constraints

alter table 表名 drop foreign key FK_ID;

4. The relationship between index and constraints

The index is database-oriented It is used for query optimization and other operations, while constraints are more of a business relationship.

Usually, when you create a unique constraint, you automatically obtain a unique index. This is because the database thinks that when uniquely checking the database, it will be faster if there is an index on the field, so when you create a unique constraint, a unique index is created by default. . Similarly, a common primary key is a unique constraint and an index. But for constraints such as not null, the database will not create an index.

The above is the detailed content of The usage and difference between indexes and constraints in MySql. For more information, please follow other related articles on the PHP Chinese website!