MySQL establishes foreign KEY constraints

http://www.jzxue.com/shujuku/mysql/201109/06-8742.html MySQL The syntax for establishing foreign KEY constraints is too obscure to understand, and has to write down notes.

1. Create a foreign key when building a table

CREATE TABLE table_s (  ' id ' int (one) not null auto_increment,  ' column_name_from ' int (one) not null,  ' name ' Varc Har () is not NULL,  PRIMARY key (' id '),  INDEX k_name (' Column_name_from '),  CONSTRAINT fk_name FOREIGN KEY ( Column_name_from) REFERENCES Other_table_name (column_name_to) on UPDATE CASCADE) Engine=innodb;

2. Add a foreign key to an existing table

ALTER TABLE TABLE_NAME ADD constraint fk_name foreign Key (Column_name_from) references other_table_name (column _name_to) on UPDATE cascade;

The foreign key is "Fk_name", and if Column_name_to does not have an index, an index named "Fk_name" is established;

3. Delete foreign keys

Delete the foreign key first, and then delete the index.

ALTER TABLE table_name drop FOREIGN key fk_name;
ALTER TABLE table_name drop key fk_name;

If you delete the index before deleting the foreign key, you are prompted #1025 –error on rename of './abc/#sql-fa9_2a9a1′to './abc/table_name ' (errno:150)

4. View the name of the foreign key

Show CREATE TABLE table_name;

5. Building an Index

CREATE INDEX index_name on table_name (COLUMN_NAME);
Or
ALTER TABLE table_name ADD index index_name (column_name);

MySQL establishes foreign KEY constraints