[打卡]2024.5.22 各种约束

-- UNIQUE 唯一性约束
CREATE DATABASE a;
use a;
SELECT DATABASE();

-- 复合约束
CREATE TABLE b(
id int,
`name` VARCHAR(15),
`password` VARCHAR(25),
CONSTRAINT UNIQUE(`name`,`password`)
);

DESC b;



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

SELECT * from b;

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

-- 删除约束
desc b;

ALTER TABLE b
drop INDEX uk_b_name_pwd;

-- PRIMARY KEY 主键约束

-- 一个表中最多只能有一个主键约束

-- 在 CREATE TABLE 时添加约束
CREATE TABLE b2(
id int PRIMARY KEY, #列级约束
last_name VARCHAR(15),
salary int,
email VARCHAR(10)
);

desc b2

-- 一旦某个字段成为主键约束，则这个字段非空，用于唯一的标识表中的一条记录


CREATE TABLE b3(
id int,
last_name VARCHAR(15),
salary int,
email VARCHAR(10),
-- 表级约束
CONSTRAINT pr_b3_id PRIMARY KEY(id)
);

desc b3;

SELECT * from information_schema.TABLE_CONSTRAINTS
where TABLE_name='b3';

insert into b3
VALUES(1,'Tom',5000,'abc@163.com');

SELECT * from b3;

-- 复合主键约束
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,'TomNB','abc');

insert into fuhe
VALUES(1,'TomNB',NULL);