P68 唯一性约束

3.唯一性约束

作用：

1.在create table时添加约束

#4.1 在create table时添加约束
CREATE TABLE test2(
id INT UNIQUE, #列级约束
last_name VARCHAR(15),
email VARCHAR(25),
salary DECIMAL(10,2),

#表级约束  起名
CONSTRAINT uk_test2_email UNIQUE(email)
);

DESC test2;

SELECT * FROM information_schema.table_constraints
WHERE table_name = 'test2';

#在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同

INSERT INTO test2(id,last_name,email,salary)
VALUES(1,'Tom','tom@126.com',4500);

#错误：id重复 Duplicate entry '1' for key 'test2.id'
INSERT INTO test2(id,last_name,email,salary)
VALUES(1,'Tom1','tom1@126.com',4600);
#错误：email重复 Duplicate entry 'tom@126.com' for key 'test2.email'
INSERT INTO test2(id,last_name,email,salary)
VALUES(2,'Tom1','tom@126.com',4600);

#可以向声明为unique的字段上添加null值，可以多次添加null值
INSERT INTO test2(id,last_name,email,salary)
VALUES(2,'Tom1',NULL,4600);

INSERT INTO test2(id,last_name,email,salary)
VALUES(3,'Tom2',NULL,4600);		

SELECT *FROM test2;

2.在alter table时添加约束

#4.2 在alter table时添加约束

DESC test2;

UPDATE test2
SET salary=5000
WHERE id =3;
#方式1：
ALTER TABLE test2
ADD CONSTRAINT uk_test2_sal UNIQUE(salary);
#方式2：
ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;

3.复合的唯一性约束

#4.3 复合的唯一性约束
CREATE TABLE USER(
id INT,
`name` VARCHAR(15),
`password` VARCHAR(25),

#表级约束
CONSTRAINT uk_user_name_pwd UNIQUE(`name`,`password`)
);

INSERT INTO USER
VALUES(1,'Tom','abc');
#可以添加成功
INSERT INTO USER
VALUES(1,'Tom1','abc');

SELECT * FROM USER;

#案例：复合的唯一性约束的案例

#学生表
CREATE TABLE student(
sid INT, #学号
sname VARCHAR(20), #姓名
tel CHAR(11) UNIQUE KEY, #电话
cardid CHAR(18) UNIQUE KEY #身份证号
);

#课程表
CREATE TABLE course(
cid INT, #课程编号
cname VARCHAR(20) #课程名称
);

#选课表
CREATE TABLE student_course(
id INT,
sid INT, #学号
cid INT, #课程编号
score INT,
UNIQUE KEY(sid,cid) #复合唯一
);

INSERT INTO student VALUES(1,'张三','13710011002','101223199012015623');#成功
INSERT INTO student VALUES(2,'李四','13710011003','101223199012015624');#成功
INSERT INTO course VALUES(1001,'Java'),(1002,'MySQL');#成功

SELECT * FROM student;
SELECT * FROM course;

INSERT INTO student_course VALUES
(1, 1, 1001, 89),
(2, 1, 1002, 90),
(3, 2, 1001, 88),
(4, 2, 1002, 56);#成功

SELECT * FROM student_course;

#错误：该学生已经有成绩了Duplicate entry '2-1002' for key 'student_course.sid'
INSERT INTO student_course VALUES
(5,2,1002,67);

4.删除唯一性约束

#4.4 删除唯一性约束
/*
添加唯一性约束的列上也会自动创建唯一索引。
删除唯一约束只能通过删除唯一索引的方式删除。
删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()
中排在第一个的列名相同。也可以自定义唯一性约束名。
*/

SELECT * FROM information_schema.table_constraints
WHERE table_name = 'test2';

DESC test2;

#如何删除唯一性索引
ALTER TABLE test2
DROP INDEX last_name;

ALTER TABLE test2
DROP INDEX uk_test2_sal;