4.mysql约束

约束

约束演示

create table user(
id int primary key auto_increment comment 'ID',
name VARCHAR(10) not null unique COMMENT '姓名',
age int CHECK(age>0 && age<=120) comment '年龄',
status char(1) DEFAULT '1' COMMENT '状态',
gender char(1) comment '年龄'
)comment '用户表';


insert into user (name,age,status,gender) values('tom1',19,'1','男'),
('tom2',25,'0','男'),('tom3',19,'1','男')

insert into user (name,age,status,gender) values('tom4',35,'1','男')
 insert into user (name,age,status,gender) values(null,35,'1','男')

外键约束

添加外键

create table 表名(
字段名 数据类型，
…
[constraint] [外键名称] foreign key (外键字段名称) references 主表(主表列名) );

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 foreign key (外键字段名称) references
主表(主表列名);

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

sql代码

create table dept(
id int primary key auto_increment comment 'ID',
name VARCHAR(50) not null unique COMMENT '部门名称'
)comment '部门表';

insert into dept (id,name) values(1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');

create table emp(
id int primary key auto_increment comment 'ID',
name VARCHAR(50) not null  COMMENT '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
salary   int COMMENT '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导id',
dept_id int comment '部门id'
)comment '员工表';


insert into emp(id,name,age,job,salary,entrydate,managerid,dept_id)values
(1,'金庸',66,'总裁',20000,'2000-01-01',null,5),
(2,'张无忌',20,'经理',12500,'2005-12-01',1,1),
(3,'杨晓',33,'开发',8400,'2000-11-02',2,1),
(4,'韦一笑',48,'开发',11800,'2002-02-05',3,1),
(5,'常虹',43,'开发',10500,'2004-09-07',4,1)

alter table emp  add constraint fk_emp_dept_id foreign key (dept_id) REFERENCES dept(id);

alter table emp drop FOREIGN key  fk_emp_dept_id;

alter table emp  add constraint fk_emp_dept_id foreign key (dept_id) REFERENCES dept(id) on update set null on delete set null;