sqltest.sql

系统主要以一电脑公司进销存管理为基本要求

/* Microsoft SQL Server - Scripting			*/
/* Server: 屠蔚华的计算机					*/
/* Database: Test					*/
/* Creation Date 99-7-2 12:45:53 			*/

set quoted_identifier on
GO

/****** Object:  Table dbo.Retreat    Script Date: 99-7-2 12:45:54 ******/
if exists (select * from sysobjects where id = object_id('dbo.Retreat') and sysstat & 0xf = 3)
	drop table "dbo"."Retreat"
GO

/****** Object:  Table dbo.Input    Script Date: 99-7-2 12:45:54 ******/
if exists (select * from sysobjects where id = object_id('dbo.Input') and sysstat & 0xf = 3)
	drop table "dbo"."Input"
GO

/****** Object:  Table dbo.Output    Script Date: 99-7-2 12:45:54 ******/
if exists (select * from sysobjects where id = object_id('dbo.Output') and sysstat & 0xf = 3)
	drop table "dbo"."Output"
GO

/****** Object:  Table dbo.Store    Script Date: 99-7-2 12:45:54 ******/
if exists (select * from sysobjects where id = object_id('dbo.Store') and sysstat & 0xf = 3)
	drop table "dbo"."Store"
GO

/****** Object:  Table dbo.Product    Script Date: 99-7-2 12:45:54 ******/
if exists (select * from sysobjects where id = object_id('dbo.Product') and sysstat & 0xf = 3)
	drop table "dbo"."Product"
GO

/****** Object:  Table dbo.Businessman    Script Date: 99-7-2 12:45:54 ******/
if exists (select * from sysobjects where id = object_id('dbo.Businessman') and sysstat & 0xf = 3)
	drop table "dbo"."Businessman"
GO

/****** Object:  Table dbo.Customer    Script Date: 99-7-2 12:45:54 ******/
if exists (select * from sysobjects where id = object_id('dbo.Customer') and sysstat & 0xf = 3)
	drop table "dbo"."Customer"
GO

/****** Object:  Table dbo.Factory    Script Date: 99-7-2 12:45:54 ******/
if exists (select * from sysobjects where id = object_id('dbo.Factory') and sysstat & 0xf = 3)
	drop table "dbo"."Factory"
GO

/****** Object:  Table dbo.Businessman    Script Date: 99-7-2 12:45:54 ******/
CREATE TABLE "dbo"."Businessman" (
	"b_id" varchar (10) NOT NULL ,
	"b_name" varchar (20) NOT NULL ,
	"b_addr" varchar (40) NULL ,
	"b_phone" varchar (20) NULL ,
	"b_limitsalary" numeric(10, 2) NOT NULL ,
	"b_workyear" "int" NOT NULL ,
	"b_trades" numeric(12, 2) NOT NULL ,
	CONSTRAINT "PK_Businessman_1__14" PRIMARY KEY  CLUSTERED 
	(
		"b_id"
	)
)
GO

/****** Object:  Table dbo.Customer    Script Date: 99-7-2 12:45:54 ******/
CREATE TABLE "dbo"."Customer" (
	"c_id" varchar (10) NOT NULL ,
	"c_name" varchar (20) NOT NULL ,
	"c_addr" varchar (40) NULL ,
	"c_phone" varchar (20) NULL ,
	CONSTRAINT "PK_Customer_1__14" PRIMARY KEY  CLUSTERED 
	(
		"c_id"
	)
)
GO

/****** Object:  Table dbo.Factory    Script Date: 99-7-2 12:45:55 ******/
CREATE TABLE "dbo"."Factory" (
	"f_id" varchar (10) NOT NULL ,
	"f_name" varchar (20) NOT NULL ,
	"f_manager" varchar (20) NULL ,
	"f_addr" varchar (40) NULL ,
	"f_phone" varchar (20) NULL ,
	CONSTRAINT "PK_Factory_1__14" PRIMARY KEY  CLUSTERED 
	(
		"f_id"
	)
)
GO

/****** Object:  Table dbo.Product    Script Date: 99-7-2 12:45:55 ******/
CREATE TABLE "dbo"."Product" (
	"p_id" varchar (10) NOT NULL ,
	"f_id" varchar (10) NOT NULL ,
	"p_name" varchar (20) NOT NULL ,
	"p_model" varchar (20) NULL ,
	CONSTRAINT "PK_Product_1__14" PRIMARY KEY  CLUSTERED 
	(
		"p_id"
	),
	CONSTRAINT "FK_Product_2__14" FOREIGN KEY 
	(
		"f_id"
	) REFERENCES "dbo"."Factory" (
		"f_id"
	)
)
GO

/****** Object:  Table dbo.Input    Script Date: 99-7-2 12:45:55 ******/
CREATE TABLE "dbo"."Input" (
	"i_id" varchar (10) NOT NULL ,
	"p_id" varchar (10) NOT NULL ,
	"i_count" "int" NOT NULL ,
	"i_inprice" numeric(10, 2) NOT NULL ,
	"i_date" "datetime" NOT NULL ,
	CONSTRAINT "PK_Input_1__14" PRIMARY KEY  CLUSTERED 
	(
		"i_id"
	),
	CONSTRAINT "FK_Input_2__14" FOREIGN KEY 
	(
		"p_id"
	) REFERENCES "dbo"."Product" (
		"p_id"
	)
)
GO

/****** Object:  Table dbo.Output    Script Date: 99-7-2 12:45:55 ******/
CREATE TABLE "dbo"."Output" (
	"o_id" varchar (10) NOT NULL ,
	"c_id" varchar (10) NOT NULL ,
	"p_id" varchar (10) NOT NULL ,
	"b_id" varchar (10) NOT NULL ,
	"o_count" "int" NOT NULL ,
	"r_count" "int" NULL CONSTRAINT "DF_Output_r_count_1__14" DEFAULT (0),
	"o_price" numeric(10, 2) NOT NULL ,
	"o_date" "datetime" NOT NULL ,
	CONSTRAINT "PK_Output_2__14" PRIMARY KEY  CLUSTERED 
	(
		"o_id"
	),
	CONSTRAINT "FK_Output_3__14" FOREIGN KEY 
	(
		"b_id"
	) REFERENCES "dbo"."Businessman" (
		"b_id"
	),
	CONSTRAINT "FK_Output_4__14" FOREIGN KEY 
	(
		"p_id"
	) REFERENCES "dbo"."Product" (
		"p_id"
	),
	CONSTRAINT "FK_Output_5__14" FOREIGN KEY 
	(
		"c_id"
	) REFERENCES "dbo"."Customer" (
		"c_id"
	)
)
GO

/****** Object:  Table dbo.Store    Script Date: 99-7-2 12:45:55 ******/
CREATE TABLE "dbo"."Store" (
	"s_id" varchar (10) NOT NULL ,
	"p_id" varchar (10) NOT NULL ,
	"s_inprice" numeric(10, 2) NOT NULL ,
	"s_count" "int" NOT NULL ,
	CONSTRAINT "PK_Store_1__14" PRIMARY KEY  CLUSTERED 
	(
		"s_id"
	),
	CONSTRAINT "FK_Store_2__14" FOREIGN KEY 
	(
		"p_id"
	) REFERENCES "dbo"."Product" (
		"p_id"
	)
)
GO

/****** Object:  Table dbo.Retreat    Script Date: 99-7-2 12:45:55 ******/
CREATE TABLE "dbo"."Retreat" (
	"r_id" varchar (10) NOT NULL ,
	"o_id" varchar (10) NOT NULL ,
	"r_count" "int" NOT NULL ,
	"r_date" "datetime" NOT NULL ,
	CONSTRAINT "PK_Retreat_1__14" PRIMARY KEY  CLUSTERED 
	(
		"r_id"
	),
	CONSTRAINT "FK_Retreat_2__14" FOREIGN KEY 
	(
		"o_id"
	) REFERENCES "dbo"."Output" (
		"o_id"
	)
)
GO