[打卡]谷假伟——约束2

SELECT DATABASE();

CREATE DATABASE dbseat;

USE dbseat;

# UNIQE 的复合约束

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,

salary INT,

last_name VARCHAR(15) PRIMARY KEY,

email VARCHAR(25)

);

CREATE TABLE test2(

id INT PRIMARY KEY,

salary INT,

last_name VARCHAR(15),

email VARCHAR(25)

);

DESC test2;

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

CREATE TABLE test3(

id INT, #列级约束

salary INT,

last_name VARCHAR(15),

email VARCHAR(25),

#表级约束

CONSTRAINT pm_test3_id PRIMARY KEY (id) # 没有必要取名字

);

# 这个primary 比较重要所以不管取什么名字都不会显示

SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = 'test3';

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

SELECT * FROM test2;

# 主键唯一

INSERT INTO test2
VALUES(1,3000,'Tom2','tom@qq.com');

# id 不能为空

INSERT INTO test2
VALUES(null,3000,'Tom8','tom@qq.com');

# 复合主键约束

CREATE TABLE test8(

id INT, #列级约束

salary INT,

last_name VARCHAR(15),

email VARCHAR(25),

#表级约束

PRIMARY KEY (last_name,salary) # 没有必要取名字
# 实际中是id

);

DESC test8;


INSERT INTO test8
VALUES(1,3000,'Tom2','tom@qq.com');

SELECT * FROM test8;

INSERT INTO test8
VALUES(1,3000,'Tom1','tom@qq.com');

# 主键不能为空

INSERT INTO test8
VALUES(1,NULL,'Tom2','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;