[打卡]郭佳豪——唯一性、主键约束

SELECT DATABASE();

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(1,'Tom2',30000,'tom@qq,com');

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)
);

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;