myqqdb.sql

一个即时通信工具

use master
go

--建立数据库
create database MyQQ	

go
use MyQQ   
go


--用户表
create table Users
(
		Id					int identity(10000,1)		not null,			--主键
		LoginPwd			varchar(50) 			not null,			--
		FriendshipPolicyId	int						not null,			--	
		NickName			varchar(50)  			not null,			--
		FaceId 				int			 			not null,			--
		Sex					varchar(50)				not null,			--
		Age					int						not null,			--
		Name 				varchar(50) 			not null,			--
		StarId 				int			 			not null,			--
		BloodTypeId			int			 			not null			--
		
)
go

--好友表
create table Friends
(		
		Id					int identity(1,1)		not null,			--主键
		HostId				int						not null,			--
		FriendId			int						not null			--	
	
)
go 

--好友策略表
create table FriendshipPolicy
(		
		Id					int identity(1,1)		not null,			--主键
		FriendshipPolicy	varchar(50) 			not null			--
)
go 

--星座表
create table Star
(		
		Id					int identity(1,1)		not null,			--主键
		Star				varchar(50) 			not null			--		
)
go 

--血型表
create table BloodType
(		
		Id					int identity(1,1)		not null,			--主键
		BloodType			varchar(50) 			not null			--
)
go 

--消息表
create table Messages
(		
		Id					int identity(1,1)		not null,			--主键
		FromUserId			int						not null,			--
		ToUserId			int						not null,			--	
		Message				varchar(50)  			not null,			--
		MessageTypeId		int			 			not null,			--
		MessageState		int						not null,			--
		MessageTime			datetime				not null			--
)
go 

--消息类型表
create table MessageType
(		
		Id					int identity(1,1)		not null,			--主键
		MessageType			varchar(50) 			not null			--
)
go 


--为表添加约束

alter table FriendshipPolicy add constraint PK_FriendshipPolicyId primary key(Id)		
go
alter table Star add constraint PK_StarId primary key(Id)		
go
alter table BloodType add constraint PK_BloodTypeId primary key(Id)       
go
alter table MessageType add constraint PK_MessageTypeId primary key(Id)		
go
alter table Friends  add constraint PK_FriendsId primary key(Id)
go

alter table Users
   add constraint PK_UsersId primary key(Id),
       constraint FK_FriendshipPolicyId foreign key(FriendshipPolicyId) References FriendshipPolicy(Id),
       constraint FK_BloodTypeId foreign key(BloodTypeId) References BloodType(Id),
       constraint FK_StarId foreign key(StarId) References Star(Id)
go

alter table Messages
   add constraint PK_MessagesId primary key(Id),
       constraint FK_MessageTypeId foreign key(MessageTypeId) References MessageType(Id)	
go



------初始化基础数据-------
insert into FriendshipPolicy values('允许任何人加我为好友')
insert into FriendshipPolicy values('需要身份验证才能加我为好友')
insert into FriendshipPolicy values('不允许任何人加我为好友')
go

insert into MessageType values('普通聊天消息')
insert into MessageType values('请求添加好友消息')
insert into MessageType values('反馈同意添加消息')
insert into MessageType values('反馈拒绝添加消息')
go

insert into BloodType values('O型血')
insert into BloodType values('A型血')
insert into BloodType values('B型血')
insert into BloodType values('AB型血')
go

insert into Star values('天马座')
insert into Star values('处女座')
insert into Star values('射手座')
insert into Star values('双鱼座')
go