版本:
Your MySQL connection id is 14
Serverversion: 8.0.11 MySQL Community Server - GPL
准备:
create table orders(orders_id int primary key auto_increment,total int,count float,time time);
create table product(product_id int primary key auto_increment,name varchar(20),price float,category_id int);
create table orderitem(orders_id int,product_id int,foreign key(orders_id) references orders(orders_id),foreign key(product_id) references product(product_id));
一、查看表的信息
show create table orderitem;
mysql> show create table orderitem;
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orderitem | CREATE TABLE `orderitem` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`orders_id` int(11) DEFAULT NULL,
`product_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `orders_id` (`orders_id`),
KEY `product_id` (`product_id`),
CONSTRAINT `orderitem_ibfk_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`orders_id`),
CONSTRAINT `orderitem_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=gbk |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
CONSTRAINT `orderitem_ibfk_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`orders_id`),
CONSTRAINT `orderitem_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`)
这两句表示的是该表的外键;
二、删除
mysql> alter table orderitem drop foreign key orderitem_ibfk_1;
mysql> alter table orderitem drop foreign key orderitem_ibfk_2;