[打卡]张博研——唯一性约束

CREATE DATABASE dbtest;

use dbtest;

#UNIQUE 唯一性约束

#UNIQUE 的复合约束

CREATE TABLE USER(

id int,

`name` VARCHAR(15),

`password` VARCHAR(25),

#表级约束

CONSTRAINT uk_user_name_pwd 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
WHERE table_name='USER';

#删除唯一性约束

ALTER TABLE USER
DROP INDEX uk_user_name_pwd;


#PRIMARY KEY 主键约束

#CREATE TABLE 时添加约束


#一个表中，最多只能有一个主键约束
CREATE TABLE test1(

id int PRIMARY KEY,

last_name VARCHAR(15),

salary int,

email VARCHAR(25)

);


DESC test1;

#主键约束的特征：非空且唯一，用于唯一的标识表中的一条记录

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;

INSERT into test2
VALUES(NULL,'Tom8',80000,'tom@qq.com');

#复合主键约束

CREATE TABLE test8(

id int,

last_name VARCHAR(15),

salary int,

email VARCHAR(25),

#表级约束

PRIMARY KEY (last_name,salary)

);

CREATE TABLE test10(

id int,

last_name VARCHAR(15),

salary int,

email VARCHAR(25)

);

ALTER TABLE test10
add PRIMARY KEY (id);


ALTER TABLE test10
drop PRIMARY KEY;