问题描述:
建立一个表customers_info:
use market;
CREATE TABLE customers(
c_num int (11) PRIMARY KEY auto_increment not NULL UNIQUE,
c_name VARCHAR(50),
c_contact VARCHAR(50),
c_city VARCHAR(50),
c_birth DATETIME not NULL
);
SELECT *FROM customers;
-- 将c_customers移到c_birth后面
alter TABLE customers MODIFY c_contact VARCHAR(50) AFTER c_birth ;
-- 将c_name类型改为varchar(70)
ALTER TABLE customers MODIFY c_name VARCHAR(70);
-- 将c_contact改为c_phone
ALTER TABLE customers CHANGE c_contact c_phone VARCHAR(50);
-- 增加c_gender,char(1)
ALTER table customers ADD c_gender CHAR(1);
-- 修改表名,customers_info
ALTER table customers RENAME customers_info;
SELECT *FROM customers_info;
-- 删除字段c_city
ALTER table customers_info DROP c_city;
-- 修改存储引擎MyISAM
-- 先查看存储引擎
SHOW CREATE TABLE customers_info ;
ALTER table customers_info ENGINE=MyISAM;
再建立一个表orders:
use market;
CREATE TABLE orders(
o_num int (11) PRIMARY KEY auto_increment NOT NULL UNIQUE,
o_date DATE,
c_id VARCHAR(70),
CONSTRAINT fk_cus_ord FOREIGN KEY(c_id) REFERENCES customers_info(c_num)
);
要求将orders表在c_id字段上添加外键约束,关联customers的主键c_num;c_num。
结果报错:
ERROR 1005 : Can't create table 'market.orders' (errno: 150)
解决方法:
1,修改表orders中c_id字段的数据类型,因为和customers的主键c_num;c_num类型不一致;
2,由于customers修改了存储类型,所以讲存储类型改回来:ALTER table customers_info ENGINE=INNODB;
3,重写表orders(注意customers已经改名为customers_info)。
ALTER table customers_info ENGINE=INNODB;
use market;
CREATE TABLE orders(
o_num int (11) PRIMARY KEY auto_increment NOT NULL UNIQUE,
o_date DATE,
c_id VARCHAR(70),
CONSTRAINT fk_cus_ord FOREIGN KEY(c_id) REFERENCES customers_info(c_num)
);
运行OK。