NNNNNNNNNN

SELECT DATABASE();

CREATE DATABASE dbtest;

USE dbtest;

# UNIQUE 唯一性约束 复合约束

CREATE TABLE USER(
id INT,
`name` VARCHAR(15),
`password` VARCHAR(25),
CONSTRAINT uk_user_name_pwd UNIQUE (`name`,`password`)
);

DESC USER;

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

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

INSERT INTO USER
VALUES(2,'Tom2','abc');

#删除唯一性约束

ALTER TABLE USER
DROP INDEX uk_user_name_pwd;

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

DESC USER;

# PRIMARY KEY 主键约束
# CREATE TABLE 时增加约束
#一个表中最多只能有一个primary key 主键
CREATE TABLE test61(
id INT PRIMARY KEY,
last_name VARCHAR(15),
salary INT,
email VARCHAR(25)
);
DESC test61;
CREATE TABLE test62(
id INT,
last_name VARCHAR(15),
salary INT,
email VARCHAR(25),
CONSISTENT pm_test62_id PRIMARY KEY(id)
);
#主键没有必要起名字
#主键约束的特征 非空且唯一 用于唯一的标识表中的一条记录 
#
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'test61';

INSERT INTO test61
VALUES(1,'Sam',3000,'sam@qq.com');

SELECT * FROM test61;

INSERT INTO test61
VALUES(10,'Sam',30000,'sam1@qq.com');

#复合主键约束
CREATE TABLE test63(
id INT,
last_name VARCHAR(15),
salary INT,
email VARCHAR(25),
PRIMARY KEY(last_name,salary)
);

DESC test63;

INSERT INTO test63
VALUES(1,'Sam',3000,'sam@qq.com');
SELECT * FROM test63;

INSERT INTO test63
VALUES(1,'Sam1',3000,'sam@qq.com');
SELECT * FROM test63;


INSERT INTO test63
VALUES(1,NULL,NULL,'sam@qq.com');
SELECT * FROM test63;

#在 ALTER TABLE 时添加约束
CREATE TABLE test64(
id INT,
last_name VARCHAR(15),
salary INT,
email VARCHAR(25)
);
DESC test64;

ALTER TABLE test64
ADD PRIMARY KEY(id);

#删除主键约束(实际开发 不会去删除主键约束)

ALTER TABLE test64
DROP PRIMARY KEY;

DESC test64;