[打卡]约束9

SELECT DATABASE();

CREATE DATABASE dbtest;

use dbtest;

#唯一性约束

#UNIQUE的复核约束
CREATE TABLE user(
id int,
`name`VARCHAR(15),
`PASSWORD` VARCHAR(25),
#表级约束
CONSTRAINT uk_user_name_pad UNIQUE(`name`,`PASSWORD`)
);

SELECT * FROM user;

INSERT into user
VALUES(1,'Tom','abc');

INSERT into user
VALUES(1,'Tom1','abc');

desc user;
SELECT * FROM information_schema.TABLE_CONSTRAINTS;#查所有的约束
#单独查
SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_name='user';
#删除唯一性约束

ALTER table user
drop index uk_user_name_pwd;

#PRIMARY key 主键约束
CREATE table test1(
id int PRIMARY key,
last_name VARCHAR(15)/*删PRIMARY key*/,
salary int,
email VARCHAR(25)
);#运行不成功，一个表中只能有一个主键约束
#主键约束的特征，非空且唯一，用于唯一的标识表中的一条记录
CREATE table test2(
id int,
last_name VARCHAR(15),
salary int,
email VARCHAR(25)
#表级约束
CONSTRAINT pm_test2_id PRIMARY key(id)#没有必要取名字，不管怎么取都不以自命名为约束的名字
);
desc test2;

SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_name='test2';

INSERT into test2
VALUES(1,'Tom',3000,'tom@qq.com');

SELECT * FROM test2;
VALUES(1,'Tom2',30000,'tom@qq.com');

SELECT * FROM test2;
VALUES(null,'Tom8',80000,'tom@qq.com');#id不能为空

CREATE table test8(
id int,
last_name VARCHAR(15),
salary int,
email VARCHAR(25)
#表级约束
PRIMARY key(last_name,salary)
);
desc test8;
INSERT into test8
VALUES(1,'Tom',3000,'tom@qq.com');
SELECT * from test8;
INSERT into test8
VALUES(1,'Tom1',3000,'tom@qq.com');
INSERT into test8
VALUES(1,'Tom1',null,'tom@qq.com');
#在alter table
CREATE table test10(
id int,
last_name VARCHAR(15),
salary int,
email VARCHAR(25)
);
desc test10;

ALTER table test10
add PRIMARY key(id);
#如何删除主键约束(在实际开发中，不会去删除表中的主键约束)
ALTER table test10
drop PRIMARY key;

desc test10;