[打卡]MySQL——约束

#UNIQUE 唯一性约束

CREATE DATABASE dbtest;

USE dbtest;

SELECT DATABASE();

#复合约束

CREATE TABLE USER(
id INT,
`name` VARCHAR(15),
`password` VARCHAR(25),
CONSTRAINT uk_user_name_pwd 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;

INSERT INTO USER
VALUES(1,'Tom1','abc');

#删除约束

ALTER TABLE USER
DROP INDEX uk_user_name_pwd;

#PRIMARY KEY 主键约束(一个表中最多只能有一个主键约束)
#在CREATE TABLE 时添加约束

CREATE TABLE test3(

id INT PRIMARY KEY, #列级约束

last_name VARCHAR(15),

salary INT,

email VARCHAR(10)
);

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;

SELECT * FROM information_schema.table_constraints
WHERE table_name ='test4';

INSERT INTO test4
VALUES(1,'Tom',5000,'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');