赵梓焓-主键约束

SELECT DATABASE();

CREATE DATABASE dbtest;

USE dbtest;

#UNIQUE 唯一性约束

#UNIQUE 的复合约束

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,

last_name VARCHAR(15),

salary INT,

email VARCHAR(25)

);

DESC test1;

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

CREATE TABLE test2(

id INT,

last_name VARCHAR(15),

salary INT,

email VARCHAR(25),

#表级约束

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

);

DESC test2;

SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'test2';

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

SELECT * FROM test2;

INSERT INTO test2
VALUES(NULL,'tom8',80000,'tom8@qq.com');

#复合主键约束
CREATE TABLE test8(

id INT,

last_name VARCHAR(15),

salary INT,

email VARCHAR(25),

#表级约束

PRIMARY KEY(last_name,salary)

);

DESC test8;

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

SELECT * FROM test8;

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

INSERT INTO test8
VALUES(1,'tom1',NULL,'tom@qq.com');

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;