javaicq_mssql.sql

使用Java环境开发的即使通讯系统, 并结合SQL.

/* ============================================================ */
/*   Database name:  JAVAICQ                                    */
/*   DBMS name:      Microsoft SQL Server 6.x                   */
/*   Created on:     2005-3-6  13:50                            */
/* ============================================================ */

if exists (select 1
            from  sysobjects
           where  name = 'TBL_MESSAGE'
            and   type = 'U')
   drop table TBL_MESSAGE
go

if exists (select 1
            from  sysobjects
           where  name = 'TBL_FRIEND'
            and   type = 'U')
   drop table TBL_FRIEND
go

if exists (select 1
            from  sysobjects
           where  name = 'TBL_USER'
            and   type = 'U')
   drop table TBL_USER
go

/* ============================================================ */
/*   Table: TBL_USER                                            */
/* ============================================================ */
create table TBL_USER
(
    FLD_USERID          varchar(6)            not null,
    FLD_NICKNAME        varchar(20)           null    ,
    FLD_IMAGE           int                   null    ,
    FLD_SEX             int                   null    ,
    FLD_AGE             int                   null    ,
    FLD_PWD             varchar(8)            null    ,
    FLD_COUNTRY         varchar(16)           null    ,
    FLD_PROVINCE        varchar(10)           null    ,
    FLD_CITY            varchar(20)           null    ,
    FLD_QUESTION        varchar(20)           null    ,
    FLD_ANSWER          varchar(20)           null    ,
    FLD_PROTECTEDEMAIL  varchar(20)           null    ,
    FLD_CARDNO          varchar(20)           null    ,
    FLD_TRUENAME        varchar(20)           null    ,
    FLD_BLOODTYPE       varchar(2)            null    ,
    FLD_SCHOOL          varchar(20)           null    ,
    FLD_OCCUPATION      varchar(20)           null    ,
    FLD_HOMEPAGE        varchar(20)           null    ,
    FLD_DESCRIPTION     varchar(60)           null    ,
    FLD_CURRENTIP       varchar(20)           null    ,
    FLD_CURRENTPORT     int                   null    ,
    FLD_STATE           int                   null    ,
    constraint PK_TBL_USER primary key (FLD_USERID)
)
go

/* ============================================================ */
/*   Table: TBL_FRIEND                                          */
/* ============================================================ */
create table TBL_FRIEND
(
    FLD_USERID          varchar(6)            not null,
    FLD_FRIENDID        varchar(6)            not null,
    FLD_ROLE            int                   null    ,
    constraint PK_TBL_FRIEND primary key (FLD_USERID, FLD_FRIENDID)
)
go

/* ============================================================ */
/*   Index: FRIENDSHIP_RELATION_FK                              */
/* ============================================================ */
create index FRIENDSHIP_RELATION_FK on TBL_FRIEND (FLD_USERID)
go

/* ============================================================ */
/*   Table: TBL_MESSAGE                                         */
/* ============================================================ */
create table TBL_MESSAGE
(
    FLD_MESSAGEID       int                   not null,
    FLD_USERID          varchar(6)            null    ,
    FLD_SEND            varchar(6)            null    ,
    FLD_RECIVE          varchar(6)            null    ,
    FLD_DATE            char(20)              null    ,
    FLD_CONTENT         varchar(100)          null    ,
    constraint PK_TBL_MESSAGE primary key (FLD_MESSAGEID)
)
go

/* ============================================================ */
/*   Index: OFFLINE_MESSAGE_RELATION_FK                         */
/* ============================================================ */
create index OFFLINE_MESSAGE_RELATION_FK on TBL_MESSAGE (FLD_USERID)
go

alter table TBL_FRIEND
    add constraint FK_TBL_FRIE_FRIENDSHI_TBL_USER foreign key  (FLD_USERID)
       references TBL_USER (FLD_USERID)
go

alter table TBL_MESSAGE
    add constraint FK_TBL_MESS_OFFLINE_M_TBL_USER foreign key  (FLD_USERID)
       references TBL_USER (FLD_USERID)
go