In depth analysis of the mysql foreign key association problem

Today, I continued to read the book of mysql database development recommended by the teacher. When I saw the foreign key association problem of innodb database, I met a problem. It was written in the book that the parent table could be modified to synchronize to the foreign key of the child table, but I failed in my own experiment.

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` FOREIGN 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 | 2012-01-09 09:18:33 |
+---------+----------+------------+---------------------+
1 row in set (0.01 sec)
mysql> select * from country;
+------------+---------+---------------------+
| country_id | country | last_update         |
+------------+---------+---------------------+
|          1 | chen    | 2012-01-09 09:16:38 |
+------------+---------+---------------------+

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` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`))

The problem above is that the country_id field cannot be changed because of the association.
Then I looked at the book again and found that there was no foreign key constraint mode of innodb (cascade,set null,no action,restrict) in my sql statement. I felt that this was where I went wrong.
But how to join the way of association, the Internet for a long time there is no suitable method. Just look for it yourself, the way the teacher said, right? help1 point 1 point finally found how to change the method, the document function is very powerful ah

| 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,...)

After writing a lot of mistakes, I don't know where to start

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:~$ perror 121
OS error code 121:  Remote I/O error
MySQL error code 121: Duplicate key on write or update
 
Can't create table 'test.icity' (errno: 150)----- I've indexed it here. The word on the Internet is: field type and foreign key index 

Here is the re-establishment of a table icity, the result is ok, the summary may be due to the field type problem, but my alter problem is still not solved:

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 `icity` (
  `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), we finally got it done. First, drop dropped the foreign key in the table, and then add. Ha ha...

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)