In-depth explanation of mysql foreign key association issues
BitsCN.com continues to read 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.
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 |
+ ------------ + --------- + --------------------- +
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.
| 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.
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:
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 ......
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)
BitsCN.com
