MySQL中约束的作用是对表中的数据进行限定,保证数据的正确性,完整性,有效性。
分类:(1)主键约束 primary key(2)非空约束 not NULL (3)唯一约束 unique (4)外键约束 foreign key
1.非空约束:限定数据非空,如果为空则不可以添加数据。
添加语法:(1)在创建表的时候添加: 列名 数据类型 NOT NULL
发现可以插入id为空的数据,但在现实生活中id不为空 那么就要去限定id:id INT NOT NULL,
(2)表已经创建完成时的非空约束:用到以前的修改语句,对name来进行限定非空
删除非空约束语法:与第二个创建方法类似,倒过来用就对了
发现姓名可以为空了。
2.唯一约束:限定列值不能重复
添加语法与之前的非空约束一样,但是删除不一样,语法为 alter table drop index 列名,之前的删除方法没有用,虽然执行成功了,但是还是唯一的
注意:NULL值表示不确定,因此唯一约束限定的列值可以有多个NULL。
3.主键约束:就是非空且唯一。并且一个表只能有一个主键列,主键是表中记录的唯一标识,同样添加方法同上,但是删除不同。
语法:alter table 表名drop primary key;那么问题来了,为什么删除不指定哪个列呢,那是因为之前说过,主键只有一个,不用去区分。
发现又可以添加重复的id了,也可以为空。
4.自动增长:某一列数值类型的,使用auto-increment可以用来完成自增,经常配合主键来使用也可以不用;
语法:列名 数据类型 primary key auto_increment;
注意:可以手动增长,也可以自动增长,而且只与上一条记录有关,把上一条的自增记录加一即为本行的数据;
我在插入的时候id写的是null但是查看表的时候却是8,再看下面的例子,我自己添加了一个id为12的数据,然后再次添加id为null的数据
发现自增的数字是12+1=13,因此自增数据的数值只与前一个数据的值有关。
5.外键约束
新建一个表:
CREATE TABLEemp1 (
idINT PRIMARY KEYAUTO_INCREMENT,
NAMEVARCHAR (30),
ageINT,
dep_nameVARCHAR(30),
dep_locationVARCHAR(30)
);INSERT INTO emp1(NAME,age,dep_name,dep_location)VALUES('张三',20,'研发部','广州');INSERT INTO emp1(NAME,age,dep_name,dep_location)VALUES('李四',21,'研发部','广州');INSERT INTO emp1(NAME,age,dep_name,dep_location)VALUES('王五',20,'研发部','广州');INSERT INTO emp1(NAME,age,dep_name,dep_location)VALUES('老王',20,'销售部','深圳');INSERT INTO emp1(NAME,age,dep_name,dep_location)VALUES('大王',22,'销售部','深圳');INSERT INTO emp1(NAME,age,dep_name,dep_location)VALUES('小王',18,'销售部','深圳');SELECT * FROM emp1;
但是发现数据有冗余,其实可以做一个表的拆分,拆成两个表,一个存放员工信息,一个存放部门信息。
--main table
CREATE TABLEdepartment(
idINT PRIMARY KEYAUTO_INCREMENT,
department_nameVARCHAR(30),
department_locationVARCHAR(30)
);--vice table
CREATE TABLEemployee(
idINT PRIMARY KEYAUTO_INCREMENT,
NAMEVARCHAR(20),
ageINT,
dep_idINT --外键对应主表的主键
);INSERT INTO department VALUES(NULL,'研发部','广州'),(NULL,'销售部','深圳');INSERT INTO employee (NAME,age,dep_id)VALUES ('张三',20,1);INSERT INTO employee (NAME,age,dep_id)VALUES ('李四',21,1);INSERT INTO employee (NAME,age,dep_id)VALUES ('王五',21,1);INSERT INTO employee (NAME,age,dep_id)VALUES ('老王',21,2);INSERT INTO employee (NAME,age,dep_id)VALUES ('大王',21,2);INSERT INTO employee (NAME,age,dep_id)VALUES ('二王',21,2);SELECT * FROMdepartment;SELECT * FROM employee;
但是想把研发部删除,居然真的删除了,但是有人还是属于研发部,不符合逻辑,应该先把部门员工开掉,然后再去删除部门,这样数据就不正确了。
把两个表放入新构架设计器
发现这两个表没有任何的关联,但是要长生关联,因此要使用外键约束。
语法:创建表的时候create table 表名 (
.....
.....
外键列
constraint 外键名称 foreign key 外键列名称 reference 主表名称 (主表主键名称)【可以是主键,也可以是被唯一约束的列】
);
先删除两个表
--main table
CREATE TABLEdepartment(
idINT PRIMARY KEYAUTO_INCREMENT,
department_nameVARCHAR(30),
department_locationVARCHAR(30)
);--vice table
CREATE TABLEemployee(
idINT PRIMARY KEYAUTO_INCREMENT,
NAMEVARCHAR(20),
ageINT,
dep_idINT,CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCESdepartment (id)
);INSERT INTO department VALUES(NULL,'研发部','广州'),(NULL,'销售部','深圳');INSERT INTO employee (NAME,age,dep_id)VALUES ('张三',20,1);INSERT INTO employee (NAME,age,dep_id)VALUES ('李四',21,1);INSERT INTO employee (NAME,age,dep_id)VALUES ('王五',21,1);INSERT INTO employee (NAME,age,dep_id)VALUES ('老王',21,2);INSERT INTO employee (NAME,age,dep_id)VALUES ('大王',21,2);INSERT INTO employee (NAME,age,dep_id)VALUES ('二王',21,2);SELECT * FROMdepartment;SELECT * FROM employee;
发现结果没有变化,但是不能删除研发部了,因为有另外一张表引用着它。
再打开构架图发现已经有线段连接着两个表。属于一对多的关系
如果再添加员工信息但是如果部门不是1,2而是5,也会报错,保证数据的正确性,改为2号就可以
外键让表与表产生关系从而保证数据的正确性。
删除外键:语法:alter table 表名 drop foreign key 外键名;
ALTER TABLE employee DROP FOREIGN KEY emp_dep_fk;
删除之后可以随意更改更部门标号
在表创建后添加外键:alter table employee add constraint 外键名称 foreign key 外键列名称 reference 主表名称 (主表主键名称)【可以是主键,也可以是被唯一约束的列】;
ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department (id);
发现又不能添加不合格的部门编号了。
6.级联操作
看上面的表如果想要更改部门id但是却不能更改,因为他在employee表中关联着数据,但是如果想要做到这个操作需要进行多步骤,先要把id置为NULL,然后再将NULL置为你想要改的数字,很麻烦,因此就出现了级联更新。
语法
create table 表名 (
.....
.....
外键列
constraint 外键名称 foreign key 外键列名称 reference 主表名称 (主表主键名称)【可以是主键,也可以是被唯一约束的列】on update cascade
);
当然也可在表创建之后添加与上面相同
先删除两个表
然后执行下列语句
--main table
CREATE TABLEdepartment(
idINT PRIMARY KEYAUTO_INCREMENT,
department_nameVARCHAR(30),
department_locationVARCHAR(30)
);--vice table
CREATE TABLEemployee(
idINT PRIMARY KEYAUTO_INCREMENT,
NAMEVARCHAR(20),
ageINT,
dep_idINT);INSERT INTO department VALUES(NULL,'研发部','广州'),(NULL,'销售部','深圳');INSERT INTO employee (NAME,age,dep_id)VALUES ('张三',20,1);INSERT INTO employee (NAME,age,dep_id)VALUES ('李四',21,1);INSERT INTO employee (NAME,age,dep_id)VALUES ('王五',21,1);INSERT INTO employee (NAME,age,dep_id)VALUES ('老王',21,2);INSERT INTO employee (NAME,age,dep_id)VALUES ('大王',21,2);INSERT INTO employee (NAME,age,dep_id)VALUES ('二王',21,2);SELECT * FROMdepartment;SELECT * FROMemployee;DROP TABLEdepartment;DROP TABLEemployee;ALTER TABLE employee DROP FOREIGN KEYemp_dep_fk;ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department (id) ON UPDATE CASCADE;
这样在更改部门id的时候就会把与其关联的都改掉。
将销售部的id改为5,然后发现没有报错,查看employee表之后发现,他们的dep_id也随之更改
有更新当然要有删除:级联删除
语法
create table 表名 (
.....
.....
外键列
constraint 外键名称 foreign key 外键列名称 reference 主表名称 (主表主键名称)【可以是主键,也可以是被唯一约束的列】on delete cascade
);
--main table
CREATE TABLEdepartment(
idINT PRIMARY KEYAUTO_INCREMENT,
department_nameVARCHAR(30),
department_locationVARCHAR(30)
);--vice table
CREATE TABLEemployee(
idINT PRIMARY KEYAUTO_INCREMENT,
NAMEVARCHAR(20),
ageINT,
dep_idINT);INSERT INTO department VALUES(NULL,'研发部','广州'),(NULL,'销售部','深圳');INSERT INTO employee (NAME,age,dep_id)VALUES ('张三',20,1);INSERT INTO employee (NAME,age,dep_id)VALUES ('李四',21,1);INSERT INTO employee (NAME,age,dep_id)VALUES ('王五',21,1);INSERT INTO employee (NAME,age,dep_id)VALUES ('老王',21,2);INSERT INTO employee (NAME,age,dep_id)VALUES ('大王',21,2);INSERT INTO employee (NAME,age,dep_id)VALUES ('二王',21,2);SELECT * FROMdepartment;SELECT * FROMemployee;DROP TABLEdepartment;DROP TABLEemployee;ALTER TABLE employee DROP FOREIGN KEYemp_dep_fk;ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department (id) ON DELETE CASCADE;
这是将研发部一列删除,这是去employee表发现,有关于研发部的数据都没有了
注意:级联删除存在弊端,如果删除则与其关联的数据全部删除,会造成数据的丢失,要在使用之前考虑清楚是否适合级联删除。