MySQL table creation and foreign keys

Template data Download
https://github.com/datacharmer/test_db

1. Employees Database Installation

(1) Employees Database Introduction
Employees database for learning and testing, approx. 160M

(1) Employees installation
Official Document Installation
https://github.com/datacharmer/test_db

Simple installation
MySQL < Employees.sql

2. Tables (table)

(1) Table Introduction
Table is the core of a relational database
Table = Relationship
A table is a collection of records
The MySQL default storage engine is row-based storage

(2) A table is a collection of data
SELECT * FROM table_name limit 1;
The collection is unordered. Randomly fetching a single piece of data from the table results in no certainty.
Only the data that is taken after order by is sorted is determined.

(3) Create a table
Syntax for creating an official document table
(1) Temporary table creation
Create Database temp;
Use temp;
Create temporary table temp_a (a int);

[Email protected]:mysql.sock [temp]>show CREATE TABLE temp_a\g;
1. Row
Table:temp_a
Create table:create Temporary Table temp_a (
aInt (one) DEFAULT NULL
) Engine=innodb DEFAULT CHARSET=UTF8MB4
1 row in Set (0.00 sec)

The default storage engine is InnoDB
INSERT into temp_a values (123);
SELECT * from Temp_a;

Note: Temporary tables are session-level. The current user exits or the other user logs on to see the table
If the temporary table and the normal table have the same name, the current user can only see temporary tables with the same name

(2) Storage engine for temporary tables
Show variables like "default%tmp%";
[Email protected]:mysql.sock [temp]>show variables like "default%tmp%";
+----------------------------+--------+
| variable_name | Value |
+----------------------------+--------+
| Default_tmp_storage_engine | InnoDB |
+----------------------------+--------+
1 row in Set (0.00 sec)

Where temporary tables are stored
System Ls-l/tmp/

[Email protected]:mysql.sock [Temp]>system ls-l/data/mysql_data/| grep IB
-rw-r-----. 1 MySQL mysql 744 Feb 15:48 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Mar 14:02 ibdata1
-rw-r-----. 1 mysql mysql 4294967296 Mar 13:51 ib_logfile0
-rw-r-----. 1 mysql mysql 4294967296 Feb 15:43 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Mar 14:04 ibtmp1

Where IBTMP1 is where the table structure corresponds

Viewing the staging table size
Show variables like "innodb_temp%";

(3) View table structure
There are 3 methods.
Show CREATE table Temp_a \g;
Desc temp_a\g;
Show table status like "Temp_a" \g;

(4) ALTER TABLE
ALTER TABLE temp_a add column B char (10);
ALTER TABLE temp_a drop column B;
Note: When the table record is very large, ALTER TABLE takes time and affects performance.

View official website Documentation ONLINE DDL

3. Foreign KEY Index

(1) Introduction of foreign keys
Official documents

CREATE TABLE Product (
Category INT not NULL,
ID INT not NULL,
Price DECIMAL,
PRIMARY KEY (category, id))
Engine=innodb;

CREATE TABLE Customer (
ID INT not NULL,
PRIMARY KEY (ID)
) Engine=innodb;

CREATE TABLE Product_order (
No INT not NULL auto_increment,
Product_category INT not NULL,
product_id INT not NULL,
customer_id INT not NULL,
PRIMARY KEY (NO),
INDEX (product_category, product_id),
INDEX (customer_id),
FOREIGN KEY (product_category, product_id)
REFERENCES Product (category, ID)
On the UPDATE CASCADE on the DELETE RESTRICT,
FOREIGN KEY (customer_id) REFERENCES customer (ID)) Engine=innodb;

(2) Foreign key operation
Table structure from MySQL official documentation
CREATE TABLE Parent (
ID int NOT NULL,
Primary KEY (ID)
) Engine=innodb;

CREATE TABLE Child (
ID int,
parent_id INT,
Index Par_ind (parent_id),
Foreign KEY (PARENT_ID)
References Parent (ID)
On DELETE CASCADE on UPDATE cascade
) Engine=innodb;

(1) We insert a piece of data, id=1,parent_id=1
INSERT into child values (+);

The error message is as follows:
ERROR 1452 (23000): Cannot add or update a child row:a FOREIGN KEY constraint fails ( temp . child , constraint child_ibf<br/>k_1 Foreig N KEY ( parent_id ) REFERENCES parent ( id ) on DELETE CASCADE on UPDATE CASCADE)

INSERT into parent values (1);
INSERT into child values (+);

Foreign KEY constraints, can let the data consistency update, but there will be some performance loss, online business use is not much. Typically, cascading updates and deletions are judged and implemented by the application-layer business logic.

MySQL table creation and foreign keys