P72 检查约束与默认值约束

7.检查约束

#8. check约束

create table test10(
id int,
last_name varchar(15),
salary decimal(10,2) check(salary>2000)
);

insert into test10
values(1,'Tom',2500);

#添加失败 工资低于2000，Check constraint 'test10_chk_1' is violated.
INSERT INTO test10
VALUES(1,'Tom',1500);

8.默认值约束

8.1在create table时添加约束

#9.1 在create table时添加约束
create table test11(
id int ,
last_name varchar(15),
salary decimal(10,2) default 2000
);

desc test11;

insert into test11(id,last_name,salary)
values(1,'Tom',3000);

#没有赋值时，salary默认为2000
INSERT INTO test11(id,last_name)
VALUES(2,'Tom');

select * from test11;

8.2在alter table时添加约束

#9.2 在alter table时添加约束

CREATE TABLE test12(
id INT ,
last_name VARCHAR(15),
salary DECIMAL(10,2) 
);

DESC test12;

ALTER TABLE test12
MODIFY salary DECIMAL(10,2) DEFAULT 2500;

8.3 删除默认值约束

#9.3在alter table删除约束
ALTER TABLE test12
MODIFY salary DECIMAL(10,2)