• 2009-08-25

    MySQL 建立外键约束

    Views: 19472 | No Comments

    MySQL 建立外键约束的语法太晦涩难懂了, 不得不记下笔记.

    1. 在建表时建立外键

    CREATE TABLE table_s (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `column_name_from` int(11) NOT NULL,
      `name` varchar(256) 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. 对已有的表增加外键

    alter table table_name add constraint fk_name foreign key (column_name_from) references other_table_name(column_name_to) on update cascade;

    外键是"fk_name", 如果 column_name_to 没有索引, 则会建立一个名为"fk_name"的索引;

    3. 删除外键

    先删除外键, 再删除索引.

    alter table table_name drop foreign key fk_name;
    alter table table_name drop key fk_name;

    如果在删除外键之前删除索引, 会提示 #1025 - Error on rename of './abc/#sql-fa9_2a9a1' to './abc/table_name' (errno: 150)

    4. 查看外键的名字

    show create table table_name;

    5. 建立索引

    create index index_name on table_name (column_name);

    alter table table_name add index index_name (column_name);

    Posted by ideawu at 2009-08-25 19:13:11 Tags:
|<<<12>>>| 2/2 Pages, 7 Results.