关于同时修改主键和另一个Text型字段的问题(头大了)(100分)

环境:Win2000Pro+sp3,Delphi6+update2,SQL Server2000+sp3
表结构:
表一: test10(主表)
------------------------------------------------------
字段名称 字段类型 长度 空 主键 默认
------------------------------------------------------
项目编号 varchar 20 Y PK
合同编号 varchar 20 Y PK
备注 Text N
GUID varchar 50 N (newid())

表二: test11(从表)
------------------------------------------------------
字段名称 字段类型 长度 空 主键 默认
------------------------------------------------------
项目编号 varchar 20 Y PK
合同编号 varchar 20 Y PK
文件名称 varchar 20 Y PK
GUID varchar 50 N (newid())

表一,表二通过项目编号,合同编号关联,级连更新删除

现有Client端程序:
unit Unit1;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, Provider, DBClient, DB, ADODB, StdCtrls, Grids, DBGridEh,
dxExEdtr, dxDBTLCl, dxGrClms, dxTL, dxDBCtrl, dxDBGrid, ExtCtrls,
DBCtrls, dxCntner, ComCtrls, dxInspct, dxOI;

type
TForm1 = class(TForm)
qr: TADOQuery;
ds: TDataSource;
cds: TClientDataSet;
dsp: TDataSetProvider;
Panel1: TPanel;
DBNavigator1: TDBNavigator;
PageControl1: TPageControl;
TabSheet1: TTabSheet;
dxRTTIInspector1: TdxRTTIInspector;
TabSheet2: TTabSheet;
dxRTTIInspector2: TdxRTTIInspector;
TabSheet3: TTabSheet;
dxRTTIInspector3: TdxRTTIInspector;
TabSheet4: TTabSheet;
dxRTTIInspector4: TdxRTTIInspector;
dxDBGrid1: TdxDBGrid;
Panel2: TPanel;
Edit1: TEdit;
Edit2: TEdit;
Label1: TLabel;
Button2: TButton;
Button1: TButton;
dxDBGrid1Column1: TdxDBGridMaskColumn;
dxDBGrid1Column2: TdxDBGridMaskColumn;
dxDBGrid1Column3: TdxDBGridMemoColumn;
dxDBGrid1GUID: TdxDBGridMaskColumn;
cds1: TClientDataSet;
Memo1: TMemo;
Splitter1: TSplitter;
TabSheet5: TTabSheet;
dxRTTIInspector5: TdxRTTIInspector;
Button3: TButton;
Button4: TButton;
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure cds1AfterInsert(DataSet: TDataSet);
procedure dspUpdateError(Sender: TObject;
DataSet: TCustomClientDataSet; E: EUpdateError;
UpdateKind: TUpdateKind; var Response: TResolverResponse);
procedure Button3Click(Sender: TObject);
procedure Button4Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
cds.Active:=false;
cds.CommandText := Edit1.Text;
cds.Active:=true;
cds1.Active:=false;
cds1.XMLData := cds.XMLData;
cds1.Active:=true;
cds.Active:=false;
Edit2.Text:='0';
Memo1.Lines.Text:='Error Infomation';
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
cds.Active:=false;
cds.CommandText := Edit1.Text + ' WHERE 1<>1 ';
cds.Active:=true;
cds.XMLData := cds1.XMLData;
try
Edit2.Text := IntToStr(cds.ApplyUpdates(0));
except
on e:Exception do
begin
Memo1.Lines.Clear;
Memo1.Lines.Append(Memo1.Lines.Append(E.Message);
end;
end;
if StrToInt(Edit2.Text) = 0 then
cds1.MergeChangeLog;
cds.Active:=false;
end;

function GetGUID(): string;
var
GUID: TGUID;
i: integer;
begin
CreateGUID(GUID);
Result := GUIDToString(GUID);
Delete(Result, Pos('{', Result), 1);
Delete(Result, Pos('}', Result), 1);
end;

procedure TForm1.cds1AfterInsert(DataSet: TDataSet);
begin
cds1.FieldByName('GUID').AsString := GetGUID
end;

procedure TForm1.dspUpdateError(Sender: TObject;
DataSet: TCustomClientDataSet; E: EUpdateError; UpdateKind: TUpdateKind;
var Response: TResolverResponse);
begin
Memo1.Lines.Text:=E.Message;

end;

procedure TForm1.Button3Click(Sender: TObject);
begin
cds.Active:=false;
qr.Active:=false;
cds.CommandText := Edit1.Text;
cds.Active:=true;
qr.Active:=true;
cds1.Active:=false;
cds1.XMLData := cds.XMLData;
cds1.Active:=true;
cds.Active:=false;
qr.Active:=false;
Edit2.Text:='0';
Memo1.Lines.Text:='Error Infomation';

end;

procedure TForm1.Button4Click(Sender: TObject);
begin
cds.Active:=false;
qr.Active:=false;
cds.CommandText := Edit1.Text + ' WHERE 1<>1 ';
cds.Active:=true;
qr.Active:=true;
cds.XMLData := cds1.XMLData;
try
Edit2.Text := IntToStr(cds.ApplyUpdates(0));
except
on e:Exception do
Memo1.Lines.Text:=E.Message;
end;
if StrToInt(Edit2.Text) = 0 then
cds1.MergeChangeLog;
cds.Active:=false;
qr.Active:=false;

end;

end.

问题:当同时修改"合同编号"和"备注"时,报错roject Project1.exe raised exception class EOleException with message '查询处理器未能用优化程序生成查询计划，因为查询无法同时更新聚集键和 text、ntext 或 image 列。'. Process stopped. Use Step or Run to continue.
跟踪发现是SQLServer的异常:8629

当将级连修改删除去掉后,修改可以成功.

各位老大,救命啊


另附数据库脚本
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_test11_Test10]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[test11] DROP CONSTRAINT FK_test11_Test10
GO

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

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

CREATE TABLE [dbo].[Test10] (
[工程编号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[合同编号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[备注] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[GUID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[test11] (
[工程编号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[合同编号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[序号] [int] IDENTITY (1, 1) NOT NULL ,
[备注] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[GUID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[ID] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Test10] WITH NOCHECK ADD
CONSTRAINT [PK_Test10] PRIMARY KEY CLUSTERED
(
[工程编号],
[合同编号]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test11] WITH NOCHECK ADD
CONSTRAINT [PK_test11] PRIMARY KEY CLUSTERED
(
[工程编号],
[合同编号],
[序号]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test11] ADD
CONSTRAINT [DF_test11_GUID] DEFAULT (newid()) FOR [GUID]
GO

ALTER TABLE [dbo].[test11] ADD
CONSTRAINT [FK_test11_Test10] FOREIGN KEY
(
[工程编号],
[合同编号]
) REFERENCES [dbo].[Test10] (
[工程编号],
[合同编号]
) ON DELETE CASCADE ON UPDATE CASCADE
GO

dfm文件
object Form1: TForm1
Left = 45
Top = 155
Width = 861
Height = 540
Caption = 'Form1'
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
PixelsPerInch = 96
TextHeight = 13
object Splitter1: TSplitter
Left = 560
Top = 0
Width = 4
Height = 513
Cursor = crHSplit
Align = alRight
end
object Panel1: TPanel
Left = 0
Top = 0
Width = 560
Height = 513
Align = alClient
TabOrder = 0
object DBNavigator1: TDBNavigator
Left = 1
Top = 487
Width = 558
Height = 25
DataSource = ds
Align = alBottom
TabOrder = 0
end
object dxDBGrid1: TdxDBGrid
Left = 1
Top = 96
Width = 558
Height = 391
Bands = <
item
end>
DefaultLayout = True
HeaderPanelRowCount = 1
KeyField = 'GUID'
SummaryGroups = <>
SummarySeparator = ', '
Align = alClient
TabOrder = 1
DataSource = ds
Filter.Criteria = {00000000}
LookAndFeel = lfFlat
OptionsBehavior = [edgoAutoSort, edgoDragScroll, edgoEditing, edgoEnterShowEditor, edgoEnterThrough, edgoImmediateEditor, edgoTabs, edgoTabThrough, edgoVertThrough]
OptionsDB = [edgoCancelOnExit, edgoCanDelete, edgoCanInsert, edgoCanNavigation, edgoConfirmDelete, edgoLoadAllRecords, edgoUseBookmarks]
OptionsView = [edgoAutoWidth, edgoBandHeaderWidth, edgoUseBitmap]
object dxDBGrid1Column1: TdxDBGridMaskColumn
BandIndex = 0
RowIndex = 0
FieldName = #24037#31243#32534#21495
end
object dxDBGrid1Column2: TdxDBGridMaskColumn
BandIndex = 0
RowIndex = 0
FieldName = #21512#21516#32534#21495
end
object dxDBGrid1Column3: TdxDBGridMemoColumn
BandIndex = 0
RowIndex = 0
FieldName = #22791#27880
end
object dxDBGrid1GUID: TdxDBGridMaskColumn
BandIndex = 0
RowIndex = 0
FieldName = 'GUID'
end
end
object Panel2: TPanel
Left = 1
Top = 1
Width = 558
Height = 95
Align = alTop
TabOrder = 2
DesignSize = (
558
95)
object Label1: TLabel
Left = 142
Top = 35
Width = 27
Height = 13
Caption = 'Errors'
end
object Edit1: TEdit
Left = 3
Top = 5
Width = 550
Height = 21
Anchors = [akLeft, akTop, akRight]
TabOrder = 0
Text = 'SELECT '#24037#31243#32534#21495','#21512#21516#32534#21495','#22791#27880',GUID FROM test10'
end
object Edit2: TEdit
Left = 172
Top = 30
Width = 31
Height = 21
TabOrder = 1
Text = '0'
end
object Button2: TButton
Left = 5
Top = 60
Width = 56
Height = 25
Caption = 'Up&amp;date'
TabOrder = 2
OnClick = Button2Click
end
object Button1: TButton
Left = 5
Top = 35
Width = 56
Height = 25
Caption = '&amp;Query'
TabOrder = 3
OnClick = Button1Click
end
object Memo1: TMemo
Left = 205
Top = 30
Width = 352
Height = 64
Align = alCustom
Anchors = [akLeft, akTop, akRight]
Ctl3D = True
Lines.Strings = (
'Error Infomation')
ParentCtl3D = False
TabOrder = 4
end
object Button3: TButton
Left = 61
Top = 35
Width = 75
Height = 25
Caption = 'Query(New)'
TabOrder = 5
OnClick = Button3Click
end
object Button4: TButton
Left = 61
Top = 60
Width = 75
Height = 25
Caption = 'Update(New)'
TabOrder = 6
OnClick = Button4Click
end
end
end
object PageControl1: TPageControl
Left = 564
Top = 0
Width = 289
Height = 513
ActivePage = TabSheet1
Align = alRight
TabIndex = 0
TabOrder = 1
object TabSheet1: TTabSheet
Caption = 'qr'
object dxRTTIInspector1: TdxRTTIInspector
Left = 0
Top = 0
Width = 281
Height = 485
InspectedObject = qr
Align = alClient
TabOrder = 0
DividerPos = 140
GridColor = clBtnShadow
Flat = True
PaintStyle = ipsCategorized
ShowRowHint = True
end
end
object TabSheet2: TTabSheet
Caption = 'dsp'
ImageIndex = 1
object dxRTTIInspector2: TdxRTTIInspector
Left = 0
Top = 0
Width = 281
Height = 425
InspectedObject = dsp
Align = alClient
TabOrder = 0
DividerPos = 140
GridColor = clBtnShadow
Flat = True
PaintStyle = ipsCategorized
ShowRowHint = True
end
end
object TabSheet3: TTabSheet
Caption = 'cds'
ImageIndex = 2
object dxRTTIInspector3: TdxRTTIInspector
Left = 0
Top = 0
Width = 281
Height = 485
InspectedObject = cds
Align = alClient
TabOrder = 0
DividerPos = 140
GridColor = clBtnShadow
Flat = True
PaintStyle = ipsCategorized
ShowRowHint = True
end
end
object TabSheet5: TTabSheet
Caption = 'cds1'
ImageIndex = 4
object dxRTTIInspector5: TdxRTTIInspector
Left = 0
Top = 0
Width = 281
Height = 485
InspectedObject = cds1
Align = alClient
TabOrder = 0
DividerPos = 140
GridColor = clBtnShadow
Flat = True
PaintStyle = ipsCategorized
ShowRowHint = True
end
end
object TabSheet4: TTabSheet
Caption = 'ds'
ImageIndex = 3
object dxRTTIInspector4: TdxRTTIInspector
Left = 0
Top = 0
Width = 281
Height = 485
InspectedObject = ds
Align = alClient
TabOrder = 0
DividerPos = 140
GridColor = clBtnShadow
Flat = True
PaintStyle = ipsCategorized
ShowRowHint = True
end
end
end
object qr: TADOQuery
CacheSize = 1000
ConnectionString =
'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security In' +
'fo=False;User ID=sa;Initial Catalog=HTGC;Data Source=XXB001'
Parameters = <>
Left = 15
Top = 120
end
object ds: TDataSource
DataSet = cds1
Left = 50
Top = 170
end
object cds: TClientDataSet
Aggregates = <>
FieldDefs = <
item
Name = #24037#31243#32534#21495
DataType = ftString
Size = 20
end
item
Name = #21512#21516#32534#21495
DataType = ftString
Size = 20
end
item
Name = #22791#27880
DataType = ftMemo
end
item
Name = 'GUID'
DataType = ftString
Size = 50
end>
IndexDefs = <>
Params = <>
ProviderName = 'dsp'
StoreDefs = True
Left = 75
Top = 120
end
object dsp: TDataSetProvider
DataSet = qr
Constraints = True
Options = [poAllowCommandText]
OnUpdateError = dspUpdateError
Left = 45
Top = 120
end
object cds1: TClientDataSet
Aggregates = <>
CommandText = 'SELECT '#24037#31243#32534#21495','#21512#21516#32534#21495','#22791#27880',GUID FROM Tb_Cntr0010'
FieldDefs = <
item
Name = #24037#31243#32534#21495
DataType = ftString
Size = 20
end
item
Name = #21512#21516#32534#21495
DataType = ftString
Size = 20
end
item
Name = #22791#27880
DataType = ftMemo
end
item
Name = 'GUID'
DataType = ftString
Size = 50
end>
IndexDefs = <>
Params = <>
ProviderName = 'dsp'
StoreDefs = True
AfterInsert = cds1AfterInsert
Left = 20
Top = 170
end
end

老兄,不是我说你, 怎么把数据库设计成这样了,
本来很简单的一个问题搞得很别扭,
我想很少有人愿意设计一个可能会需要修改主键值的表吧,
现在的结构也不符合范式啊

给个说法吧,该怎么设计数据库呢呢?用GUID作主键吗?

我们公司的数据库主键用的都是自增型字段，该字段要没有实际意义。
这样在程序中就不会要修改主键

系统为多层结构:Client+WebService+COM+
自增型字段做主键肯定不行

Client+WebService+COM+
架构倒是很吓人,
建议你在主表中设一个整数或够长的数值字段作关键字 自己写套机制保证唯一,
从表根据这个字段关联,
项目编号,合同编号只出现在主表中
不要设为主键,

我也想过用GUID做主键，用它作关联，但改动太大，100多个表呢

换个思路，海阔天空