用户管理模块数据库设计

--用户管理模块数据库设计

drop database UsersDBTest
--------------创建数据库----------------------------
create database UsersDBTest  --sqlserver这一句话就可以创建数据库，其它默认值。

on  primary  -- 默认就属于primary文件组,可省略
(
/*--数据文件的具体描述--*/
    name='UsersDBTest_data',  -- 主数据文件的逻辑名称
    filename='E:\DB\UsersDBTest_data.mdf', -- 主数据文件的物理名称和地址
    size=5mb, --主数据文件的初始大小
    maxsize=100mb, -- 主数据文件增长的最大值
    filegrowth=15%--主数据文件的增长率
)
log on
(
/*--日志文件的具体描述,各参数含义同上--*/
    name='UsersDBTest_log',
    filename='E:\DB\UsersDBTest_log.ldf',
    size=2mb,
    filegrowth=1mb
  )
  
  
  use  UsersDBTest -- 使用数据库
  
 -------------创建用户、角色、权限表------------------ 
  create table UserInfo
  (
	  ID int identity(1,1) not null, 
	  Uname nvarchar(max) not null,
	  Pwd nvarchar(max) not null,
	  ShowName nvarchar(max) null,

  );
    
  create table RoleInfo
  (
	  ID int identity(1,1) not null,
	  RoleName nvarchar(max) not null,
  );


  create table ActionInfo
  (
	  ID int identity(1,1)  not null,
	  ActionName nvarchar(max) not null
  );
  
  --------------创建关系表----------------
  
  create table UserInfoRoleInfo
  (
	  UserInfo_ID int not null,
	  RoleInfo_ID int not null
	  
  );
  
  create table RoleInfoActionInfo
  (
	  RoleInfo_ID int not null,
	  ActionInfo_ID int not null
  );
  
  --如何让不拥有A角色的人拥有A角色的某个权限，让拥有A角色所有权限的人失去某个权限?
  --跨角色授权或削权
  create table R_UserInfo_ActionInfo  
  (
	  ID int identity(1,1) not null,
	  HasPermisson smallint not null,
	  UserInfo_ID int not null,
	  ActionInfo_ID int not null
  );
  
 ---------------创建所有主键约束-----------
 
 Alter table UserInfo
    add constraint PK_UserInfo  --给主键约束取别名,寓意:UserInfo表的主键约束。取别名的好处是1删除方便2为多个列定义同时约束，即主键组。
		Primary Key Clustered(ID Asc); --主键约束 ，对ID列聚集索引，根据ID升序排序
	
 Alter table RoleInfo
    add constraint PK_RoleInfo
		Primary Key Clustered(ID Asc);
	
 Alter table ActionInfo
    add constraint PK_ActionInfo 
		Primary Key Clustered(ID Asc);
	
 Alter table UserInfoRoleInfo
	add constraint PK_UserInfoRoleInfo
		Primary Key Clustered(UserInfo_ID,RoleInfo_ID Asc);
	
 Alter table RoleInfoActionInfo
	add constraint PK_RoleInfoActionInfo
		Primary Key Clustered(RoleInfo_ID,ActionInfo_ID Asc);


 Alter table R_UserInfo_ActionInfo
	add constraint PK_R_UserInfo_ActionInfo
		Primary Key Clustered(ID Asc);
		

--主键的简单创建方式：
  --create table ActionInfo
  --(
	 -- ID int identity(1,1) primary key not null,  --方式1
	 -- ActionName nvarchar(max) not null,
	 -- --primary key(ID)  --方式2

  --);
  
--添加主键
 -- alter table UserInfo add constraint PK_UserInfo primary key(ID,Uname) --主键组
--删除主键： 
--Alter table UserInfo drop constraint PK_UserInfo 
--必须先删除相关外键约束



		
------------创建所有外键--------------------

 Alter table UserInfoRoleInfo
	add constraint FK_UserInfoRoleInfo_UserInfo --给外键取别名
		Foreign Key(UserInfo_ID) References UserInfo(ID) --让外键表UserInfoRoleInfo的 UserInfo_ID列引用UserInfo表的ID列 
		On Delete no Action On Update no Action; --注意此处的Action不是ActionInfo表！！！
		
	
 Alter table UserInfoRoleInfo
	add constraint FK_UserInfoRoleInfo_RoleInfo --注意名称不同
		Foreign Key(RoleInfo_ID) References RoleInfo(ID) 
		On Delete no Action On Update no Action;
	
	
 Alter table RoleInfoActionInfo
	add constraint FK_RoleInfoActionInfo_RoleInfo 
	Foreign Key(RoleInfo_ID) References RoleInfo(ID)
	On Delete no Action On Update no Action;
	
 Alter table RoleInfoActionInfo
	add constraint FK_RoleInfoActionInfo_ActionInfo
	Foreign Key(ActionInfo_ID) References ActionInfo(ID)
	On Delete no Action On Update no Action;


	
 Alter table R_UserInfo_ActionInfo
	add constraint FK_UserInfoR_UserInfo_ActionInfo
	Foreign Key(UserInfo_ID) References UserInfo(ID)
	On Delete no Action On Update No Action;
	

  
  Alter table R_UserInfo_ActionInfo
	add constraint FK_ActionInfoR_UserInfo_ActionInfo
	Foreign Key(ActionInfo_ID) References ActionInfo(ID)
	On Delete no Action On Update No Action;
	
	 
--ON DELETE NO ACTION 指定如果试图删除某一行，而该行的键被其他表的现有行中的外键所引用，则产生错误并回滚 DELETE 语句。
--ON UPDATE NO ACTION 指定如果试图更新某一行中的键值，而该行的键被其他表的现有行中的外键所引用，则产生错误并回滚 UPDATE 语句。 
	
--外键（引用）
--作用：保持数据一致性，完整性，
--主要目的：控制存储在外键表中的数据。 
--使两张表形成关联，外键只能引用外表中的列的值或使用空值。
--1如果不使用外键，成绩表的学号字段插入一个值（比如13341321），
--但是这个值在学生表中并没有，此时数据库允许插入，并不会对插入的数据做关系检查。
--问题是这样很可能出现学生表和成绩表不对应，在整合学生信息时出现遗漏或多余等问题。
--2在设置外键的情况下，你要插入成绩表学号字段的值必须要求在学生表的学号字段能找到。 
--你要删除学生表的某个学号，必须保证成绩表中没有引用该值所在的列（外键），否则就没法删除。
--这就是所谓的保持数据的一致性和完整性。


------------创建索引------------------------	


 Create Index IX_FK_UserInfoRoleInfo_RoleInfo --给索引取名字
	on UserInfoRoleInfo(RoleInfo_ID); --需要建立索引的列

 Create Index IX_FK_RoleInfoActionInfo_ActionInfo
	on RoleInfoActionInfo(ActionInfo_ID);


 Create Index IX_FK_UserInfoR_UserInfo_ActionInfo
   on R_UserInfo_ActionInfo(UserInfo_ID);


 Create Index IX_FK_ActionInfoR_UserInfo_ActionInfo
	on R_UserInfo_ActionInfo(ActionInfo_ID);
	

	 

--外键是怎么发挥作用的？把两张表放一些数据增删改看看.