jicq database.sql

java编写完成的简单通信工具。类似于ICQ

/* ============================================================ */
/*   Database name:  JICQ                                       */
/*   DBMS name:      Microsoft SQL Server 7.x                   */
/*   Created on:     2003-6-9  17:18                            */
/* ============================================================ */

if exists (select 1
            from  sysobjects
           where  id = object_id('OfflineMsg')
            and   type = 'U')
   drop table OfflineMsg
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Friends')
            and   type = 'U')
   drop table Friends
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Users')
            and   type = 'U')
   drop table Users
go

/* ============================================================ */
/*   Table: Users                                               */
/* ============================================================ */
create table Users
(
    UserID          varchar(6)            not null,
    NickName        varchar(20)           null    ,
    Age             int                   null    ,
    Sex             char(2)               null    ,
    Password        varchar(8)            null    ,
    Country         varchar(20)           null    ,
    Province        varchar(10)           null    ,
    City            varchar(10)           null    ,
    Question        varchar(40)           null    ,
    Answer          varchar(40)           null    ,
    ProtectedEmail  varchar(40)           null    ,
    Identify        varchar(20)           null    ,
    TrueName        varchar(20)           null    ,
    Blood           char(2)               null    ,
    College         varchar(20)           null    ,
    Occupation      varchar(10)           null    ,
    HomePage        varchar(20)           null    ,
    Comment         varchar(120)          null    ,
    Status          int                   null    ,
    CurrentIP       char(15)              null    ,
    CurrentPort     int                   null    ,
    Image           int                   null    ,
    Verify          int                   null    ,
    constraint PK_USERS primary key (UserID)
)
go

/* ============================================================ */
/*   Table: Friends                                             */
/* ============================================================ */
create table Friends
(
    UserID          varchar(6)            not null,
    FriendID        varchar(6)            not null,
    Role            int                   null    ,
    constraint PK_FRIENDS primary key (UserID, FriendID)
)
go

/* ============================================================ */
/*   Index: Freindship_FK                                       */
/* ============================================================ */
create index Freindship_FK on Friends (UserID)
go

/* ============================================================ */
/*   Table: OfflineMsg                                          */
/* ============================================================ */
create table OfflineMsg
(
    OfflineMsgID    char(16)              not null,
    UserID          varchar(6)            null    ,
    SourceID        varchar(6)            null    ,
    SourceNickName  varchar(20)           null    ,
    DateTime        datetime              null    ,
    Comment         varchar(120)          null    ,
    constraint PK_OFFLINEMSG primary key (OfflineMsgID)
)
go

/* ============================================================ */
/*   Index: OfflineMessage_FK                                   */
/* ============================================================ */
create index OfflineMessage_FK on OfflineMsg (UserID)
go

alter table Friends
    add constraint FK_FRIENDS_FREINDSHI_USERS foreign key  (UserID)
       references Users (UserID)
go

alter table OfflineMsg
    add constraint FK_OFFLINEM_OFFLINEME_USERS foreign key  (UserID)
       references Users (UserID)
go