用代码创建表时定义参照完整性
--图书销售表
if not exists (select * from sysobjects where id =
object_id(N '[dbo].[BookSale] ') and OBJECTPROPERTY(id, N 'IsUserTable ') = 1)
begin
CREATE TABLE [dbo].[BookSale](
[Sn] [int] (4) COLLATE Chinese_PRC_CI_AS primary key not NULL ,
[Account] [char] (10) COLLATE Chinese_PRC_CI_AS NULL,
[SaleDate] [datetime] (8) COLLATE Chinese_PRC_CI_AS NULL,
[SumMoney] [money] (8) COLLATE Chinese_PRC_CI_AS NULL,
FOREIGN KEY [Account] REFERENCES Operator [Account],
) ON [PRIMARY]
end
各位大侠看一下上面的创建表的代码对吗？
尤其是倒数第三行，定义参照完整性那？
谢谢了！
[解决办法]
给你一个参考涵数!
'*************************************************************************
'**FunctionName:CreatTabSql
'**Input :TableName(String) -TableName
'**OutPut :NULL
'**Description :Creat New Table
'**Global Var :
'**Author :YongJun-Zou
'**Data :2007-03-20 16:23:18
'**Version :V1.0.0
'*************************************************************************
Public Sub CreatTabSql(TableName As String)
On Error GoTo ToExit 'OPEN ERROR PORT
'------------------------------------------------
'Creat New Table which is same as tEwcData
CreatTableSQL = "CREATE TABLE " & TableName & " ( "
CreatTableSQL = CreatTableSQL & "[fEwcID] [uniqueidentifier] DEFAULT newid() NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcDatMasID] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcDatNo] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcRow] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcGaiMasID] [int] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcCircuit] [varchar] (8) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcDivision] [varchar] (8) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcDivNo] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcDivCheck] [char] (1) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcDekID1] [varchar] (2) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcDekID2] [varchar] (2) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcDestination1] [varchar] (28) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcDestination2] [varchar] (28) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcClrMasID] [varchar] (4) DEFAULT '0000 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcCutPrint] [tinyint] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcMark] [decimal](18, 2) DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcLength] [decimal](18, 2) DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcSetFlag1] [char] (1) DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcForeRGB1] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcBackRGB1] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcSetFlag2] [char] (1) DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcForeRGB2] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcBackRGB2] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcSetFlag3] [char] (1) DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcForeRGB3] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcBackRGB3] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcSetFlag4] [char] (1) DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcForeRGB4] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcBackRGB4] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcSetFlag5] [char] (1) DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcForeRGB5] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcBackRGB5] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcSetFlag6] [char] (1) DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcForeRGB6] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcBackRGB6] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcSetFlag7] [char] (1) DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcForeRGB7] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcBackRGB7] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcSetFlag8] [char] (1) DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcForeRGB8] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcBackRGB8] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcSetFlag9] [char] (1) DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcForeRGB9] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcBackRGB9] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcPrint] [char] (1) DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcPrintOver] [char] (1) DEFAULT '0 ' NOT NULL , "
[解决办法]
CreatTableSQL = CreatTableSQL & "[fEwcCutPrintOver] [char] (1) DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcDemPrintOver] [char] (1) DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcUpDivision] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcUpDivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcDownDivision] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcDownDivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc01Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc01DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc02Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc02DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc03Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc03DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc04Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc04DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc05Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc05DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc06Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc06DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc07Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc07DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc08Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc08DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc09Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc09DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc10Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc10DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc11Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc11DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc12Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc12DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc13Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc13DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc14Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc14DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc15Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc15DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc16Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc16DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc17Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc17DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc18Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc18DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc19Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc19DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc20Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc20DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc21Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc21DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc22Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc22DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc23Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc23DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc24Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc24DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc25Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc25DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc26Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc26DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc27Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc27DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc28Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc28DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc29Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc29DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc30Division] [varchar] (4) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwc30DivPosition] [tinyint] DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcDeleteKbn] [char] (1) DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcMikakunin] [int] DEFAULT '0 ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcCreateDate] [datetime] DEFAULT getdate() NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcCreateName] [varchar] (40) DEFAULT ' ' NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcUpdateDate] [datetime] DEFAULT getdate() NOT NULL , "
CreatTableSQL = CreatTableSQL & "[fEwcUpdateName] [varchar] (40) DEFAULT ' ' NOT NULL "
CreatTableSQL = CreatTableSQL & ") ON [PRIMARY] "
If RunSQL(CreatTableSQL) <> R_OK Then 'ＳＱＬ実行
Call ErrMsg(1) 'エラーメッセージ
Exit Sub 'サブルーチン終了
End If
'------------------------------------------------
Exit Sub
'----------------
ToExit:
Call OutPutErrMsg( "CreatTabSql ", "frmInputData ")
End Sub
[解决办法]
参考而已,!
