mysql外键指令总结

mysql外键约束指令总结

创建表时

三种限制类型：

• restrict(no action)
• cascade
• set null

CREATE TABLE author_book( book_id VARCHAR(10) NOT NULL, author_id VARCHAR(10) NOT NULL, PRIMARY KEY(book_id, author_id), FOREIGN KEY (book_id )  REFERENCES book(id) ON DELETE CASCADE  ON UPDATE CASCADE, FOREIGN KEY (author_id )  REFERENCES author(id) ON DELETE CASCADE ON UPDATE CASCADE);

增加外键时

// 增加constraint namealter table author_book add constraint AB_author_book foreign key (author_id) references author(id);// 自动生成constraint namealter table author_book add foreign key (author_id) references author(id) on udpate cascade on delete cascade;

修改外键时的设置

// mysql修改外键的设置只有add和drop// 先删除后增加// 注意一下constraintname可以用show create table 查看到// 可以在添加foreign key时指定constraint namealter table author_book drop foreign key (constraintname);alter table author_book add foreign key (author_id) references author(id) on udpate cascade on delete cascade;

几个快忘记的指令

// 删除表drop table author_book;// 表的详情describe author_book;show create table author_book;// 增删查改insert into author values(...)delete from author where id="A1";select * from author ...;update author set name="BOOKNAME1" where id="A1";