taobao.sql

一个仿造淘宝的jsp网站。功能比较完善

/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2000                    */
/* Created on:     2008-8-2 19:02:14                            */
/*==============================================================*/

use master
go
if exists (select * from sysdatabases where name='taobao')
drop database taobao
go
create database taobao
go
use taobao
go
if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('follownote') and o.name = 'FK_FOLLOWNO_REFERENCE_USERINFO')
alter table follownote
   drop constraint FK_FOLLOWNO_REFERENCE_USERINFO
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('follownote') and o.name = 'FK_FOLLOWNO_REFERENCE_MASTERNO')
alter table follownote
   drop constraint FK_FOLLOWNO_REFERENCE_MASTERNO
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('masternote') and o.name = 'FK_MASTERNO_REFERENCE_USERINFO')
alter table masternote
   drop constraint FK_MASTERNO_REFERENCE_USERINFO
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('masternote') and o.name = 'FK_MASTERNO_REFERENCE_MODEINFO')
alter table masternote
   drop constraint FK_MASTERNO_REFERENCE_MODEINFO
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('orderinfo') and o.name = 'FK_ORDERINF_REFERENCE_ORDERSTA')
alter table orderinfo
   drop constraint FK_ORDERINF_REFERENCE_ORDERSTA
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('orderinfo') and o.name = 'FK_ORDERINF_REFERENCE_USERINFO')
alter table orderinfo
   drop constraint FK_ORDERINF_REFERENCE_USERINFO
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('shopinfo') and o.name = 'FK_SHOPINFO_REFERENCE_TYPEINFO')
alter table shopinfo
   drop constraint FK_SHOPINFO_REFERENCE_TYPEINFO
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('shopinfo') and o.name = 'FK_SHOPINFO_REFERENCE_USERINFO')
alter table shopinfo
   drop constraint FK_SHOPINFO_REFERENCE_USERINFO
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('shopinfo') and o.name = 'FK_SHOPINFO_REFERENCE_STATEINF')
alter table shopinfo
   drop constraint FK_SHOPINFO_REFERENCE_STATEINF
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('shopnuminfo') and o.name = 'FK_SHOPNUMI_REFERENCE_SHOPINFO')
alter table shopnuminfo
   drop constraint FK_SHOPNUMI_REFERENCE_SHOPINFO
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('shoporder') and o.name = 'FK_SHOPORDE_REFERENCE_SHOPINFO')
alter table shoporder
   drop constraint FK_SHOPORDE_REFERENCE_SHOPINFO
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('shoporder') and o.name = 'FK_SHOPORDE_REFERENCE_ORDERINF')
alter table shoporder
   drop constraint FK_SHOPORDE_REFERENCE_ORDERINF
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('userinfo') and o.name = 'FK_USERINFO_REFERENCE_PHOTOINF')
alter table userinfo
   drop constraint FK_USERINFO_REFERENCE_PHOTOINF
go

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

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

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

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

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

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

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

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

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

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

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

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

/*==============================================================*/
/* Table: follownote                                            */
/*==============================================================*/
create table follownote (
   fid                  int     identity(1,1)             not null,
   userid               int                  null,
   mid                  int                  null,
   followtitle          varchar(100)         null,
   followmessage        varchar(500)         null,
   followtime           varchar(100)         null
   constraint PK_FOLLOWNOTE primary key (fid)
)
go