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