/****** Object: Table [dbo].[单据头] ******/ CREATE TABLE [dbo].[单据头] ( [单据编号] [int] NOT NULL , [其他字段段] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO/****** Object: Table [dbo].[明细表] ******/ CREATE TABLE [dbo].[明细表] ( [单据编号] [int] NOT NULL , [明细编号] [int] NOT NULL , [其他字段] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO/****** Object: Table [dbo].[序号表] ******/ CREATE TABLE [dbo].[序号表] ( [单据编号] [int] NOT NULL , [明细编号] [int] NOT NULL , [序号] [int] NOT NULL , [其他字段] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dfsgdsg]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[a1] DROP CONSTRAINT dfsgdsg GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aa]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[aa] GO --B表 a(ID) b(規格號) c(手機號) ...........CREATE TABLE [dbo].[aa] ( [a] [varchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL , [b] [varchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL , [c] [varchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL , [d] [int] NOT NULL , [e] [varchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ) ON [PRIMARY] GOALTER TABLE [dbo].[aa] WITH NOCHECK ADD CONSTRAINT [sss] PRIMARY KEY NONCLUSTERED ( [a], [b] ) ON [PRIMARY] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[a1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[a1] GO --C表 a(ID) b(規格號) c(序號) d(手機號)........... CREATE TABLE [dbo].[a1] ( [a] [varchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL , [b] [varchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL , [c] [varchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL , [d] [int] NOT NULL , [e] [datetime] NOT NULL ) ON [PRIMARY] GOALTER TABLE [dbo].[a1] WITH NOCHECK ADD CONSTRAINT [sssss] PRIMARY KEY NONCLUSTERED ( [a], [b], [c] ) ON [PRIMARY] GOALTER TABLE [dbo].[a1] ADD CONSTRAINT [dfsgdsg] FOREIGN KEY ( [a], [b] ) REFERENCES [dbo].[aa] ( [a], [b] ) GO
单据(明细)B 假定主键 colA,colB,colC,colD(序号)单据三级库存C 假定主键 colA,colB,colC,colD(序号),colE(识别号)
-------------
A : 1 ,1 ,1
2 ,2 ,2
B: 1,1,1,1
1,1,1,2
...
c: 1,1,1,1,1
1,1,1,1,2
1,1,1,2,1
A:ID(單據號) key......
B:ID(單據號) key,規格號 key,手機號,...........
C:ID(單據號) key,規格號 key,序號 key,.........
說明:B表單據號和A表的單據號是外鍵關係;
C表單據號、規格號和B表單據號、規格號是外鍵關係.
如:
A表 ID ........
1001 ........
1002 ........
B表 ID 規格號 手機號 ...........
1001 001 A01 ...........
1001 002 A02 ...........
1002 001 A01 ...........
1002 002 A02 ...........
C表 ID 規格號 手機號 序號 ...........
1001 001 A01 1 ...........
1001 001 A01 2 ...........
1001 002 A02 1 ...........
1001 002 A02 2 ...........
1002 001 A01 1 ...........
1002 001 A01 2 ...........
1002 002 A02 1 ...........
1002 002 A02 2 ...........
CREATE TABLE [dbo].[单据头] (
[单据编号] [int] NOT NULL ,
[其他字段段] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO/****** Object: Table [dbo].[明细表] ******/
CREATE TABLE [dbo].[明细表] (
[单据编号] [int] NOT NULL ,
[明细编号] [int] NOT NULL ,
[其他字段] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO/****** Object: Table [dbo].[序号表] ******/
CREATE TABLE [dbo].[序号表] (
[单据编号] [int] NOT NULL ,
[明细编号] [int] NOT NULL ,
[序号] [int] NOT NULL ,
[其他字段] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[a1] DROP CONSTRAINT dfsgdsg
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aa]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[aa]
GO
--B表 a(ID) b(規格號) c(手機號) ...........CREATE TABLE [dbo].[aa] (
[a] [varchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[b] [varchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[c] [varchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[d] [int] NOT NULL ,
[e] [varchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[aa] WITH NOCHECK ADD
CONSTRAINT [sss] PRIMARY KEY NONCLUSTERED
(
[a],
[b]
) ON [PRIMARY]
GO
drop table [dbo].[a1]
GO
--C表 a(ID) b(規格號) c(序號) d(手機號)...........
CREATE TABLE [dbo].[a1] (
[a] [varchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[b] [varchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[c] [varchar] (10) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[d] [int] NOT NULL ,
[e] [datetime] NOT NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[a1] WITH NOCHECK ADD
CONSTRAINT [sssss] PRIMARY KEY NONCLUSTERED
(
[a],
[b],
[c]
) ON [PRIMARY]
GOALTER TABLE [dbo].[a1] ADD
CONSTRAINT [dfsgdsg] FOREIGN KEY
(
[a],
[b]
) REFERENCES [dbo].[aa] (
[a],
[b]
)
GO