mysql怎么删除默认约束_mysql修改表时添加默认约束和删除默认约束

mysql修改表时添加默认约束和删除默认约束

直接po代码和截图

#

CREATE TABLE testMyIndex2(

id int,

address VARCHAR(130),

email VARCHAR(40),

hobby VARCHAR(160),

userName VARCHAR(50)

);

#修改表时添加默认约束

#

ALTER TABLE testMyIndex2 MODIFY address VARCHAR(115) DEFAULT '江西省赣州市于都县';

SELECT * FROM testMyIndex2;

INSERT INTO testMyIndex2(id, email, hobby, userName) VALUES (1, 'ling@qq.com', '喝酒', '令狐冲');

INSERT INTO testMyIndex2(id, email, address, hobby, userName) VALUES (4, 'yang@qq.com', DEFAULT, '打架', '杨过');

SELECT * FROM testMyIndex2;

#

ALTER TABLE testMyIndex2 ALTER COLUMN address SET DEFAULT '中国江西省于都县';

SELECT * FROM testMyIndex2;

INSERT INTO testMyIndex2(id, email, hobby, userName) VALUES (2, 'wei@qq.com', '喜欢瞎掰', '韦小宝');

INSERT INTO testMyIndex2(id, email, address, hobby, userName) VALUES (3, 'zhang@qq.com', DEFAULT, '练武功', '张无忌');

SELECT * FROM testMyIndex2;

#删除默认约束(有2种方式)

#删除默认约束(方式1)

ALTER TABLE testMyIndex2 MODIFY address VARCHAR(90);

#

DESC testMyIndex2;

SELECT * FROM testMyIndex2;

#插入数据时，不会报错，address列为null

INSERT INTO testMyIndex2(id, email, address, hobby, userName) VALUES (5, 'guo@qq.com', DEFAULT, '看书', '郭靖');

SELECT * FROM testMyIndex2;

#删除默认约束(方式2)

ALTER TABLE testMyIndex2 ALTER COLUMN address DROP DEFAULT;

#

DESC testMyIndex2;

/*如果是通过第2种方式删除默认约束，那么如下这样插入数

据时会报错1364 - Field 'address' doesn't have a default value

*/

INSERT INTO testMyIndex2(id, email, address, hobby, userName) VALUES (6, 'huang@qq.com', DEFAULT, '聪明伶俐', '黄蓉');

SELECT * FROM testMyIndex2;

#

DESC testMyIndex2;

#正确插入数据

INSERT INTO testMyIndex2(id, email, address, hobby, userName) VALUES (7, 'jian@qq.com', '紫禁城', '嚣张跋扈', '建宁公主');

/*如果是通过第2种方式删除默认约束，那么如下这样插入数

据时会报错1364 - Field 'address' doesn't have a default value

*/

INSERT INTO testMyIndex2(id, email, hobby, userName) VALUES (8, 'shuang@qq.com', '武功', '双儿');

#

DESC testMyIndex2;

#可以查一下建表的信息，分析一下为什么上面的那条INSERT语句为什么无法插入数据，为什么报错?

SHOW CREATE TABLE testMyIndex2;

#

SELECT * FROM testMyIndex2;

#

INSERT INTO testMyIndex2(id, email, address, hobby, userName) VALUES (9, 'mu@qq.com', '沐王府', '小郡主', '沐剑屏');

#

SELECT * FROM testMyIndex2;