MySQL FOREIGN Key Association (Genesis new article)

Database foreign key

• 01.mysql> Show CREATE TABLE Country\g
  02.*************************** 1. Row ***************************
  Table:country.
  04.Create table:create Table ' Country ' (
  The ' 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
  10.1 row in Set (0.01 sec)
  11.
  12.mysql> Show CREATE TABLE City\g
  13.*************************** 1. Row ***************************
  Table:city.
  15.Create table:create Table ' City ' (
  city_id ' smallint (5) unsigned not NULL auto_increment,
  . ' City ' varchar (+) 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
  24.1 row in Set (0.00 sec)
  25.mysql> SELECT * from the city;
  26.+---------+----------+------------+---------------------+
  27.| city_id | City | country_id | last_update |
  28.+---------+----------+------------+---------------------+
  29.| 1 |          Hancheng | 1 | 2012-01-09 09:18:33 |
  30.+---------+----------+------------+---------------------+
  31.1 row in Set (0.01 sec)
  32.
  33.mysql> select * from country;
  34.+------------+---------+---------------------+
  35.| country_id | Country | last_update |
  36.+------------+---------+---------------------+
  37.| 1 | Chen | 2012-01-09 09:16:38 |
  38.+------------+---------+---------------------+
  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 the 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 the 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 the 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 |
  +------------+---------+---------------------+

  
  01.mysql> Update country set country_id=100 where country_id=1;
  02.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 '))
  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 the country_id field cannot be changed because of the presence of the association.

  Then I re-read the book, found that their SQL statements do not have INNODB foreign key constraints (Cascade,set Null,no action,restrict), feel that this is the problem of their own place.

  However, how to join the connection method, the Internet to find the long time there is no suitable method. Just find out for yourself, through the teacher said the method,? Help a little bit finally found a way to change, the document is very powerful.

  01.| ADD {index| KEY} [Index_name] [Index_type] (Index_col_name,...)
  02. | ADD [CONSTRAINT [symbol]]
  PRIMARY KEY [Index_type] (Index_col_name,...)
  04. | ADD [CONSTRAINT [symbol]]
  UNIQUE [index| KEY] [index_name] [Index_type] (Index_col_name,...)
  | 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,...)
  It's a lot of mistakes when I write it.

  01.mysql> ALTER TABLE city add CONSTRAINT ' City_ibfk_1 ' FOREIGN KEY (' country_id ') REFERENCES ' country ' (' country_id ') On UPDATE CASCADE;
  02.ERROR 1005 (HY000): Can ' t create table '. \test\ #sql-ed0_37.frm ' (errno:121)
  03.zhouqian@zhou: ~$ perror 121
  04.OS error code 121:remote I/O error
  05.MySQL error code 121:duplicate key on write or update
  06.
  07.Can ' t create table ' test.icity ' (errno:150)-----I have also indexed it here. The online argument is: The index of the field type and foreign key
  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 have also indexed it here. The online argument is: The index of the field type and foreign key

  Here is the re-establishment of a table icity, the results can be, the summary may be due to the field type problem, but my alter's problem is not resolved:

  01.mysql> CREATE TABLE icity (ID int not NULL, city varchar (a), country_id smallint unsigned NOT NULL, PRIMARY key (ID) , foreign key (country_id) references country (COUNTRY_ID) on UPDATE cascade) Engine=innodb;
  02.Query OK, 0 rows affected (0.11 sec)
  03.
  04.mysql> Show CREATE TABLE Icity\g
  05.*************************** 1. Row ***************************
  Table:icity.
  07.Create table:create Table ' icity ' (
  . ' ID ' int (one) not NULL,
  The "city" varchar DEFAULT NULL,
  Ten. ' 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
  15.1 row in Set (0.02 sec)
  Mysql> CREATE TABLE icity (ID int not NULL, city varchar (a), 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) is 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 the people (teachers and netizens) with the help of the finally fix, the method first drop the foreign key in the table, and then in Add. Oh......

  01.mysql> ALTER TABLE city drop FOREIGN KEY ' city_ibfk_1 ';
  02.Query OK, 0 rows affected (0.24 sec)
  03.records:0 duplicates:0 warnings:0
  04.
  05.mysql> ALTER TABLE city add FOREIGN KEY (' country_id ') REFERENCES ' country ' (' country_id ') on UPDATE CASCADE; Query OK, 0 rows affected (0.16 sec)
  06.records:0 duplicates:0 warnings:0
  07.
  08.mysql> Show CREATE TABLE City\g
  09.*************************** 1. Row ***************************
  Ten. Table:city
  11.Create table:create Table ' City ' (
  ' city_id ' smallint (5) unsigned not NULL auto_increment,
  "City" varchar (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
  21.1 row in Set (0.00 sec)

  Done

  End

  
  

MySQL FOREIGN Key Association (Genesis new article)