Today continue to see the teacher to recommend the simple and simple MySQL database development This book, see INNODB Database Foreign Key Association problem, encountered a problem, the book is written on the parent table can be modified to sync to the foreign key to the child table, but their own experiment is not able to.
Copy Code code 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 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 is 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 |
+------------+---------+---------------------+
Copy Code code as follows:
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_ib Fk_1 ' FOREIGN KEY (' country_id ') REFERENCES ' country ' (' country_id ')
The problem above is that because there is an association, it is not possible to change the country_id field.
Then I looked at the book again, and found that there was no InnoDB foreign KEY constraint (Cascade,set null,no action,restrict) in his SQL statement, and felt that this was the place where I had a problem.
But how to join the relevant way, the Internet to find a good time and there is no suitable method. To find their own, through the teacher said the method,? Help a little bit finally found how to change the method, the document is very powerful AH
Copy Code code 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,...)
After writing a lot of mistakes, can't do it.
Copy Code code 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:~$ 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 also have indexed here. The online version is: The index of field type and foreign key
Here is to re-establish a table icity, the result is possible, the summary may be due to the problem of the field type, but my alter problem is still unresolved:
Copy Code code as follows:
Mysql> CREATE TABLE icity (ID int not NULL, city varchar (m), country_id smallint unsigned NOT NULL, PRIMARY key (ID), F Oreign 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 (one) not NULL,
' City ' varchar 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)
In everyone (teachers and netizens) with the help of finally finished, the practice of dropping out of the table outside the key, and then in Add. Oh......
Copy Code code 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 is 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)
