主键
在创建表时,一般会定义一个主键来唯一标识一组信息,例如保存京东fuliuqingfeng的用户信息及其多个邮寄商品地址:
CREATE TABLE user_info(
id CHAR(36) PRIMARY KEY,
user_name VARCHAR(30) not null,
password VARCHAR(30) not null,
real_name VARCHAR(8),
mobile CHAR(11),
address VARCHAR(150)
);
执行结果:创建一个user_info表,主键为id,主键不能重复,不能为空。
INSERT INTO user_info (id,user_name,password,real_name,mobile,address) VALUES (null,'fuliuqingfeng','123456','张三','18920120206','河南安阳');
INSERT INTO user_info (id,user_name,password,real_name,mobile,address) VALUES ('12','fuliuqingfeng','123456','张三','18920120206','河南安阳');
INSERT INTO user_info (id,user_name,password,real_name,mobile,address) VALUES ('12','fuliuqingfeng','123456','张三','18920120206','河南安阳');
上面两次插入均不成功!
外键
对上述user_info表进行插入:
INSERT INTO user_info (id,user_name,password,real_name,mobile,address) VALUES ('1','fuliuqingfeng','123456','张三','18920120206','河南安阳');
INSERT INTO user_info (id,user_name,password,real_name,mobile,address) VALUES ('2','fuliuqingfeng','123456','张三','18920120206','河南安阳');
INSERT INTO user_info (id,user_name,password,real_name,mobile,address) VALUES ('3','fuliuqingfeng','123456','张三','18920120206','河南安阳');
执行结果:
可见表中user_name和password有大量冗余信息,如果个人信息字段比较多这一问题表现的越严重。
也可以将一个表分为两个表来操作:
CREATE TABLE user_info(
id CHAR(36) PRIMARY KEY,
user_name VARCHAR(30) not null,
password VARCHAR(30) not null
)
INSERT INTO user_info (id,user_name,password) VALUES('1','fuliuqingfeng','123456');
CREATE TABLE address(
id CHAR(36) PRIMARY KEY,
user_info_id CHAR(36),
real_name VARCHAR(8) not null,
mobile CHAR(11) not null,
address VARCHAR(150) not null
)
INSERT INTO address (id,user_info_id,real_name,mobile,address) VALUES('1','1','张三','18920120206','河南安阳');
INSERT INTO address (id,user_info_id,real_name,mobile,address) VALUES ('2','1','李四','18617297545','北京海淀');
INSERT INTO address (id,user_info_id,real_name,mobile,address) VALUES ('3','1','王五','17694976949','山西大同');
执行结果:
user_info表
address表
消除了大量冗余,但是如果在user_info中删除id为1的信息,那么在address表中就找不到user_info_id为1的信息属于哪个用户,并且,在address表中查入一条user_info_id不存在的信息时,该数据不完整,找不到该条地址信息属于哪个用户。
为解决上述问题我们使用外键约束。
CREATE TABLE user_info(
id CHAR(36) PRIMARY KEY,
user_name VARCHAR(30) not null,
password VARCHAR(30) not null
)
INSERT INTO user_info (id,user_name,password) VALUES('1','fuliuqingfeng','123456');
CREATE TABLE address(
id CHAR(36) PRIMARY KEY,
user_info_id CHAR(36),
real_name VARCHAR(8) not null,
mobile CHAR(11) not null,
address VARCHAR(150) not null,
CONSTRAINT address_user_info_id_fk FOREIGN KEY(user_info_id) REFERENCES user_info(id)
)
INSERT INTO address (id,user_info_id,real_name,mobile,address) VALUES('1','1','张三','18920120206','河南安阳');
INSERT INTO address (id,user_info_id,real_name,mobile,address) VALUES ('2','1','李四','18617297545','北京海淀');
INSERT INTO address (id,user_info_id,real_name,mobile,address) VALUES ('3','1','王五','17694976949','山西大同');
其中FOREIGN KEY约束用于预防破坏表之间连接的动作,即在address中有user_info_id为1的信息时,不允许删除user_name中id为1的信息,FOREIGN KEY 约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。也就是说,不允许在address中插入user_info_id的值在user_name不存在的地址信息。