P69 主键约束

4.主键约束

1.在create table时添加约束

#5.1 在create table时添加约束

#一个表中最多只能有一个主键约束
#错误：Multiple primary key defined
CREATE TABLE test3(
id INT PRIMARY KEY, #列级约束
last_name VARCHAR(15) PRIMARY KEY,
salary DECIMAL(10,2),
email VARCHAR(25)
);

#主键约束特征：非空且唯一，用于唯一的标识表中的一条记录
CREATE TABLE test4(
id INT PRIMARY KEY, #列级约束
last_name VARCHAR(15) ,
salary DECIMAL(10,2),
email VARCHAR(25)
);

CREATE TABLE test5(
id INT ,
last_name VARCHAR(15) ,
salary DECIMAL(10,2),
email VARCHAR(25),
#表级约束
CONSTRAINT pk_test5_id PRIMARY KEY(id) #没有必要起名字
);

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

INSERT INTO test4(id,last_name,salary,email)
VALUES(1,'Tom',4500,'tom@126.com');

#id重复 错误:Duplicate entry '1' for key 'test4.PRIMARY'
INSERT INTO test4(id,last_name,salary,email)
VALUES(1,'Tom',4500,'tom@126.com');
#不能是空值 错误：Column 'id' cannot be null
INSERT INTO test4(id,last_name,salary,email)
VALUES(NULL,'Tom',4500,'tom@126.com');

SELECT * FROM test4;

CREATE TABLE user1(
id INT,
NAME VARCHAR(15),
PASSWORD VARCHAR(25),

PRIMARY KEY(NAME,PASSWORD)
);

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

INSERT INTO user1
VALUES(1,'Tom1','abc');
#错误：联合的name和password都不能为null Column 'name' cannot be null
INSERT INTO user1
VALUES(1,NULL,'abc');

SELECT * FROM user1;

2.在alter table时添加约束

CREATE TABLE test6(
id INT ,
last_name VARCHAR(15) ,
salary DECIMAL(10,2),
email VARCHAR(25)
);

DESC test6;

ALTER TABLE test6
ADD PRIMARY KEY(id);

3.如何删除主键约束(在实际开发中，不会去删除表中的主键约束)

ALTER TABLE test6
DROP PRIMARY KEY;