[打卡]xgl 好多约束

# UNIQUE 唯一性约束

CREATE DATABASE school;
USE school;

SELECT DATABASE();

#复合约束
CREATE TABLE USER(
id int,
`name` VARCHAR(15),
`password` VARCHAR(25),
CONSTRAINT uk_user_name_password UNIQUE(`name`,`password`)
);
DESC user;

SELECT * from information_schema.TABLE_CONSTRAINTS
WHERE TABLE_name ='user';

INSERT into USER
VALUES(1,'tom','abc')

SELECT * from user;

#删除约束
ALTER TABLE user
drop INDEX uk_user_name_password;

# PRIMARY key 主键约束
# 一个表最多只能有一个主键约束
# CREATE TABLE 时添加约束

CREATE TABLE test3(
id int PRIMARY KEY,
last_name VARCHAR(520),
salary int,
email VARCHAR(521)
);

DESC test3;
#主键约束一旦某个字段成为主键约束，这个字段非空

CREATE TABLE test4(
id int,
last_name VARCHAR(520),
salary int,
email VARCHAR(521),
#表级约束
CONSTRAINT pr_test4_id PRIMARY key(id)#没必要取名字
);

desc test4

INSERT into test4
VALUES(1,'tom',6000,'abc@163.com');

SELECT * from test4;

#复合主键约束
CREATE TABLE fuhe(
id int,
name VARCHAR(10),
password VARCHAR(25),
PRIMARY key(name,password)
);
DESC fuhe

INSERT into fuhe
VALUES(1,'tom','abc');

SELECT * from fuhe;

INSERT into fuhe
VALUES(1,'tom6','abc');

INSERT into fuhe
VALUES(1,'tom6',null);