--新建数据库
create database QQlz
go
--用户表
use QQlz
create table userInfo(
userId int primary key identity(20120808,1),
pwdqq varchar(20) not null,
nickname varchar(20) not null,
sex nchar(1) not null check(sex='男' or sex='女'),
birthday datetime not null,
addressqq varchar(20) not null,
openzone int not null,
telephone varchar(11) null check(len(telephone)=11),
statusqq int default 0,
headphoto image null,
mood varchar(50) null,
education varchar(20) null,
job varchar(20) null
)
--分组表
create table groupQQ(
groupId int primary key identity(1,1),
groupname varchar(20) not null,
userId int not null
)
--关系表
create table relationShip(
relationId int primary key identity(1,1),
userId int not null foreign key references userInfo(userId),
friendId int not null foreign key references userInfo(userId),
groupId int not null foreign key references groupQQ(groupId)
)
--聊天记录表
create table chatLogs(
chatId int primary key identity(1,1),
relationId int foreign key references relationShip(relationId),
content Nvarchar(4000) null,
msgtime datetime not null
)