Shulou(Shulou.com)06/02 Report--

This article mainly introduces the example analysis of primary keys, foreign keys and indexes in the database, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article. Let's take a look at it.

Create table class (id int not null primary key,name char (16)); set id as the primary key

Create table student2 (id int 11) not null,name char 16 not null,class_id int 11 not null,primary key (id), key fk_class_key (class_id)

Contraint fk_class_key foreign key (class_id) references class (id); set id as the primary key, set class_id as the foreign key type, and limit the foreign key type to class_id

And associated with the id of the class table

Insert into student2 (id,name,class_id) values (1)

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdb`.`student2`, CONSTRAINT `fk_class_ key` FOREIGN KEY (`class_ id`) REFERENCES `class` (`id`))

At this point, if the class table does not exist and cannot be inserted, it is called a foreign key constraint.

Insert into class (id,name) values (1, "linux")

Insert into student2 (id,name,class_id) values (1)

If there is data in the student table associated with this class table, you cannot delete the records associated with it in the class table.

Delete from class where id = 1

ERROR1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testdb`.`student2`, CONSTRAINT `fk_class_ key` FOREIGN KEY (`class_ id`) REFERENCES `class` (`id`))

There are four ways to add an index to a data table:

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): this statement adds a primary key, which means that the index value must be unique and cannot be NULL.

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): the value that this statement creates an index must be unique (NULL may appear multiple times except for NULL).

ALTER TABLE tbl_name ADD INDEX index_name (column_list): add a normal index, where the index value can appear multiple times.

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): this statement specifies that the index is FULLTEXT for full-text indexing.

Drop index index_name on tbl_name; delete index

Alter table tbl_name drop index index_name; delete index

Tbl_name: table name

Index_name: index name

Column_list: column name

SHOW INDEX FROM table_name\ G

Show keys from tbl_name; View Index

Alter table tbl_name add priamry key (column_list); adding primary key is also an index function

Alter table tbl_name drop primary key; deletes the primary key

Thank you for reading this article carefully. I hope the article "sample Analysis of Primary Keys, Foreign Keys and Indexes in Database" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.