In-depth explanation of mysql foreign key Association issues

Today, I will continue reading the book "mysql database development" recommended by the instructor. I encountered a problem when I saw the foreign key association problem with the innodb database, in the book, you can modify the parent table and synchronize it to the foreign key of the sub-Table. However, you cannot perform this experiment. Copy codeThe Code is as follows: mysql> show create table country \ G
* *************************** 1. row ***************************
Table: country
Create Table: create table 'country '(
'Country _ id' smallint (5) unsigned not null auto_increment,
'Country' varchar (50) not null,
'Last _ Update' timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
Primary key ('country _ id ')
) ENGINE = InnoDB default charset = utf8
1 row in set (0.01 sec)
Mysql> show create table city \ G
* *************************** 1. row ***************************
Table: city
Create Table: create table 'city '(
'City _ id' smallint (5) unsigned not null auto_increment,
'City' varchar (50) not null,
'Country _ id' smallint (5) unsigned not null,
'Last _ Update' timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
Primary key ('city _ id '),
KEY 'country _ id' ('country _ id '),
CONSTRAINT 'city _ ibfk_1 'eign KEY ('country _ id') REFERENCES 'country' ('country _ id ')
) ENGINE = InnoDB default charset = utf8
1 row in set (0.00 sec)
Mysql> select * from city;
+ --------- + ---------- + ------------ + --------------------- +
| City_id | city | country_id | last_update |
+ --------- + ---------- + ------------ + --------------------- +
| 1 | hancheng | 1 | 09:18:33 |
+ --------- + ---------- + ------------ + --------------------- +
1 row in set (0.01 sec)
Mysql> select * from country;
+ ------------ + --------- + --------------------- +
| Country_id | country | last_update |
+ ------------ + --------- + --------------------- +
| 1 | chen | 09:16:38 |
+ ------------ + --------- + --------------------- +

Copy codeRun the following code: mysql> update country set country_id = 100 where country_id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails ('test/city ', CONSTRAINT 'city _ ibfk_1 'eign KEY ('country _ id') REFERENCES 'country' ('country _ id '))

The problem above is that the country_id field cannot be changed because of association.
Then I read the book again and found that My SQL statement does not have the innodb foreign key constraint (cascade, set null, no action, restrict ), I feel that this is where my problem is.
But how can we join the association method? There is no proper way to access the Internet for a long time. Just look for it by yourself, just by the way the teacher says ,? Help: I finally found a way to change it. The documentation is very powerful.Copy codeThe Code is as follows: | ADD {INDEX | KEY} [index_name] [index_type] (index_col_name ,...)
| ADD [CONSTRAINT [symbol]
Primary key [index_type] (index_col_name ,...)
| ADD [CONSTRAINT [symbol]
UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name ,...)

There are a lot of errors after I write them.Copy codeThe Code is as follows: mysql> alter table city add CONSTRAINT 'city _ ibfk_1 'foreign key ('country _ id') REFERENCES 'country' ('country _ id') on update cascade;
ERROR 1005 (HY000): Can't create table '. \ test \ # sql-ed0_37.frm' (errno: 121)
Zhouqian @ zhou :~ $ Perror121
Operating error code 121: Remote I/O error
MySQL error code 121: Duplicate key on write or update

Can't create table 'test. atomicity '(errno: 150) ----- I have created an index here. The online saying is: field type and foreign key index

Here we re-create a table, and the result is OK. The summary may be due to the field type problem, but my alter problem is still not solved:Copy codeThe Code is as follows: mysql> create table icity (id int not null, city varchar (20), country_id smallint unsigned not null, primary key (id), foreign key (country_id) references country (country_id) on update cascade) engine = innodb;
Query OK, 0 rows affected (0.11 sec)
Mysql> show create table icity \ G
* *************************** 1. row ***************************
Table: icity
Create Table: create table 'atomicity '(
'Id' int (11) not null,
'City' varchar (20) default null,
'Country _ id' smallint (5) unsigned not null,
Primary key ('id '),
KEY 'country _ id' ('country _ id '),
CONSTRAINT 'icity _ ibfk_1 'foreign key ('country _ id') REFERENCES 'country' ('country _ id') ON UPDATE CASCADE
) ENGINE = InnoDB default charset = latin1
1 row in set (0.02 sec)

With the help of everyone (teachers and netizens), I finally got it done. First drop the foreign key in the table and then add it. Haha ......Copy codeThe Code is as follows: mysql> alter table city drop foreign key 'city _ ibfk_1 ';
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql> alter table city add foreign key ('country _ id') REFERENCES 'country '('country _ id') on update cascade; Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql> show create table city \ G
* *************************** 1. row ***************************
Table: city
Create Table: create table 'city '(
'City _ id' smallint (5) unsigned not null AUTO_INCREMENT,
'City' varchar (50) not null,
'Country _ id' smallint (5) unsigned not null,
'Last _ Update' timestamp not null default CURRENT_TIMESTAMP ON update CURRENT_TIMESTAMP,
Primary key ('city _ id '),
KEY 'country _ id' ('country _ id '),
KEY 'idx _ fk_country_id '('country _ id '),
CONSTRAINT 'city _ ibfk_1 'foreign key ('country _ id') REFERENCES 'country' ('country _ id') ON UPDATE CASCADE
) ENGINE = InnoDB default charset = utf8
1 row in set (0.00 sec)