数据库_MySQL_复杂的表结构创建

本例示例下复杂的表结构创建，

 

大致分为两张表 user表 和 authority(权限表)

 

每个用户对应一种权限，默认为1(普通用户)  灵感来源：(仿照 进程的优先级, 优先级越高的进程，数字越小)

 

功能清单：

1.用户表对应的数据删除了，权限表对应的数据 也应该删除

2.新用户注册应该分配默认权限

 

示意如下：

 

 

其中用到的技术与难点：

 

1.表的级联更新与级联删除

写法截取：

CONSTRAINT `FK_authority` FOREIGN KEY (`user_id`, `user_name`) REFERENCES `user` (`user_id`, `user_name`)ON DELETE CASCADE ON UPDATE CASCADE

 

2.外键约束，非主键的外键约束，

注意事项：

1.若对应的项不是主键，

2.建立外键约束前, 需要为对应字段其建立索引，可利用key 建立，（主表和从表都需要建立）示例：

KEY `FK_authority` (`user_id`,`user_name`),

3.外键约束

 

CONSTRAINT `FK_authority` FOREIGN KEY (`user_id`, `user_name`) REFERENCES `user` (`user_id`, `user_name`) ON DELETE CASCADE ON UPDATE CASCADE

 

 

 

 

完整的SQL:

 

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50528
Source Host           : localhost:3306
Source Database       : warehouse_manager

Target Server Type    : MYSQL
Target Server Version : 50528
File Encoding         : 65001

Date: 2015-11-25 11:52:24
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `employee`
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) NOT NULL,
  `authority` int(11) DEFAULT '1' COMMENT '用户的权限',
  PRIMARY KEY (`user_id`),
  KEY `FK_authority` (`user_id`,`user_name`),
  CONSTRAINT `FK_authority` FOREIGN KEY (`user_id`, `user_name`) REFERENCES `user` (`user_id`, `user_name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES ('1', 'szh', '0');
INSERT INTO `employee` VALUES ('2', 'sss', '0');
INSERT INTO `employee` VALUES ('3', 'ssk', '1');
INSERT INTO `employee` VALUES ('4', '2', '1');
INSERT INTO `employee` VALUES ('5', '3', '1');
INSERT INTO `employee` VALUES ('6', 'sds', '1');
INSERT INTO `employee` VALUES ('7', 'sd', '1');

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) NOT NULL,
  `user_pass` varchar(50) NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_name` (`user_name`),
  KEY `user_id` (`user_id`,`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'szh', '123');
INSERT INTO `user` VALUES ('2', 'sss', '123');
INSERT INTO `user` VALUES ('3', 'ssk', '132');
INSERT INTO `user` VALUES ('4', '2', '3');
INSERT INTO `user` VALUES ('5', '3', '123');
INSERT INTO `user` VALUES ('6', 'sds', '123');
INSERT INTO `user` VALUES ('7', 'sd', '123');

 

 

 

 

 

 

后来发现user_authority表中 user_name为冗余字段，修改后的表结构如图：

 

 

创建表的sql

 

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50528
Source Host           : localhost:3306
Source Database       : warehouse_manager

Target Server Type    : MYSQL
Target Server Version : 50528
File Encoding         : 65001

Date: 2015-11-25 23:26:28
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) NOT NULL,
  `user_pass` varchar(50) NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'szh', '123');
INSERT INTO `user` VALUES ('2', 'sss', '123');
INSERT INTO `user` VALUES ('3', 'ssk', '132');
INSERT INTO `user` VALUES ('4', '2', '3');
INSERT INTO `user` VALUES ('5', '3', '123');
INSERT INTO `user` VALUES ('6', 'sds', '123');
INSERT INTO `user` VALUES ('7', 'sd', '123');

-- ----------------------------
-- Table structure for `user_authority`
-- ----------------------------
DROP TABLE IF EXISTS `user_authority`;
CREATE TABLE `user_authority` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `authority` int(11) DEFAULT '1' COMMENT '用户的权限',
  PRIMARY KEY (`user_id`),
  KEY `FK_authority` (`user_id`),
  CONSTRAINT `FK_authority` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user_authority
-- ----------------------------
INSERT INTO `user_authority` VALUES ('1', '0');
INSERT INTO `user_authority` VALUES ('2', '0');
INSERT INTO `user_authority` VALUES ('3', '0');
INSERT INTO `user_authority` VALUES ('4', '1');
INSERT INTO `user_authority` VALUES ('5', '1');
INSERT INTO `user_authority` VALUES ('6', '1');
INSERT INTO `user_authority` VALUES ('7', '1');