MySQL foreign key and cascade delete && table storage engine with CREATE INDEX && delete database and table

Messages table:

Mysql>create Table Messages (

->message_id int Auto_increment primary KEY,

->user_name varchar () NOT NULL,

->author_id int NOT NULL,

->body text,

->forum_id int not NULL);

Forums Table:

Mysql>create Table Forums (

->forum_id int Auto_increment primary KEY,

->name varchar (150),

->description text,

->owner_id int);

Two of the most common engines are MyISAM and inndb. (MyISAM is faster and table storage requires less space, InnoDB supports more robust tables and record locks required for SQL transactions);

You can specify the storage engine when you use the CREATE TABLE statement in SQL:

Mysql>create Table Messages (

->message_id int Auto_increment primary KEY,

->user_name varchar () NOT NULL,

->author_id int NOT NULL,

->body text,

->forum_id int NOT NULL

)engine InnoDB;

For a table, such as the messages table, we often want to search for the table effectively, and find the record under the conditions of a given user name (user_name).

We can do this by adding an index to the CREATE TABLE statement:

Mysql>create Table Messages (

->message_id int Auto_increment primary KEY,

->user_name varchar () NOT NULL,

->author_id int NOT NULL,

->body text,

->forum_id int NOT NULL

-Index (USER_NAME)

) engine InnoDB;

If we later find that we need to search the author_id field frequently, we can create an index after creating the table using the CREATE INDEX statement:

Create index author_id on Messages (author_id);

Foreign keys and cascading deletions::::

In the previous list, we found that the table used for the message has a foreign key that references the forums table: forums_id int NOT NULL

This means that we hope that the forums_id in messages can only be the legal identifier of forums_id from the forums table, although it is possible to add a code to verify that forums_id is legitimate when the user adds a message. But we can get the database server to do this for us by forcing a foreign key:

Foreign KEY (formus_id) references Forums (forums_id);

An error is caused if you attempt to add a forums_id field that does not represent a valid identifier from the appropriate table to the messages table.

When we want to delete a table, for example forums causes a row in the messages table to point to the forums table that is not in, and we want the database to automatically delete all messages that belong to the table when we design the Web application, then we can further modify the foreign key constraint. Let him perform a cascade delete. When a record in the parent table (Forums) is deleted, any records referenced by the foreign key of the child table (Messages) that are set to the ID of the parent record that was just deleted (for example, forums_id) are also deleted by the database engine.

Add an ON DELETE cascade to the foreign key declaration:

Foreign KEY (forums_id) references forums (forum_id)

ON DELETE Cascade

To delete a database and table:

In SQL, these tasks are performed using the drop database and drop table queries. These two queries use the names of the entities to be deleted as parameters:

drop database name;

drop table name;

MySQL foreign key and cascade delete && table storage engine with CREATE INDEX && delete database and table