createsimplebank.sql

来自「用ADO.Net实现通用数据库编程源码.rar」· SQL 代码 · 共 54 行

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Transaction_Account]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Transaction] DROP CONSTRAINT FK_Transaction_Account
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Account]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Account]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IdGen]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IdGen]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Transaction]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Transaction]
GO

CREATE TABLE [dbo].[Account] (
	[AccountId] [int] NOT NULL ,
	[Owner] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[AccountType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Balance] [money] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[IdGen] (
	[NextAccountId] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Transaction] (
	[XactType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[AccountId] [int] NOT NULL ,
	[Amount] [money] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Account] WITH NOCHECK ADD 
	CONSTRAINT [DF_Account_Balance] DEFAULT (0) FOR [Balance],
	CONSTRAINT [PK_Account] PRIMARY KEY  CLUSTERED 
	(
		[AccountId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Transaction] ADD 
	CONSTRAINT [FK_Transaction_Account] FOREIGN KEY 
	(
		[AccountId]
	) REFERENCES [dbo].[Account] (
		[AccountId]
	)
GO