リレーションシップ更新

質問

SQLSERVER 2005 EXPRESS EDITIONでKEYが変われば自動で更新するようにリレーションシップを作成したのですが、KEYが3つまではうまく更新できますがKEYが4つになると4つ目のKEY値がすべて同じ値になり重複エラーのメッセージが表示されます。製品版の2005では修正パッチがあったのですがEXPRESS EDITIONではないみたいです。2008のEXPRESS EDITIONでも試したのですが同じ結果でした。どなたか教えて下さい。(ACCESSからSQLSERVERにしようとしてリレーションシップも含めアップサイジングしたのですが更新がうまくいかなかったのでわかりました）
宜しくお願いします。

回答No.1

jamshid6 · ベストアンサー

質問の内容からは、以下のような理解をしたのですが、
TBL1 (KEY1,KEY2,KEY3,KEY4,...)
TBL2 (KEY1,KEY2,KEY3,KEY4,...)

ALTER TABLE TBL2 ADD CONSTRAINT [FK1] FOREIGN KEY (KEY1,KEY2,KEY3,KEY4) REFERENCES TBL1 (KEY1,KEY2,KEY3,KEY4)
ON UPDATE CASCADE

上記の状態で、TBL1のキーを更新する

Expressで直接またはAccessのリンクテーブル経由でも、おっしゃるような事象が確認できなかったのですが、可能ならば再現できるサンプルを示していただけませんか。

補足

ご返事有難うございます。
TBL1のKEY1を直接変更するとKEY1,KEY2,KEY3は変更されるみたいですが,KEY4が1行目のデータを引きずっているみたいです。実際のメッセージは”エラーメッセージ：制約’テーブル名＿PK’のPRIMARYKEY違反。オブジェクト’テーブル名’には重複したキーを挿入できません。ステートメントは終了されました。エラーを修正して再実行するかまたは、ESCをおして変更キャンセルしてください。”とでます。
ＴＢＬ１（１、２、３）
ＴＢＬ２（１、２、３、ＡＡＡＡ)(１、２、３、ＢＢＢＢ）
とあるときにＴＢＬ１を開きKEY1の１を変更し３にするとき上記のメッセージが出ます。実際ＳＱＬを実行しているのではなくACCESSのリンクしたテーブルを開きデータを変更したときにでます。
なぜ、KEY4だとわかったかと言えばエラーがでるのでＴＢＬ２のKEYをすべて主KEYを削除しINDEXのみの指定にしたところＴＢＬ１のKEY1,KEY2,KEY3に一致するＴＢＬ２のレコードのKEY4がすべて”ＡＡＡＡ"になっていました。ACCESSではうまく主KEYを設定してデータを変更すれば思っているように変更されていましたので同じ様にならないかと思いまして投稿させて頂きました。うまく説明できていないかも知れませんがＳＱＬＳＥＲＶＥＲを触ったことないのでなかなか判らなくて、ご迷惑をお掛けしますが宜しくお願いします。

回答No.4

jamshid6

あれ？CASCADEしていませんね。最初に
>KEYが変われば自動で更新するようにリレーションシップを作成したのですが
と書かれたのは、どこで設定をされたのでしょうか。

外部キーを右クリックして変更を選ぶと、「外部キーのリレーションシップ」の画面が開きますが、その「INSERTおよびUPDATEの設定」－「UpdateRuleの設定」は「重ねて表示」になっている必要があります。
（スクリプトでやるならば、外部キーを一旦削除して、生成したスクリプトに「ON UPDATE CASCADE」を追加して実行するのですが）

お礼

ご返事有難うございました。
UPDATE CASCADEがないので色々設定を変えているとご返事頂いた場所にINSERTおよびUPDATEの設定に動作なしとあったのでここに重ねて表示を設定しスクリプトをみると前回頂いた形と同じ用になったのでテストするとうまく処理されました。テストでうまく行ったので本番のデータで同じ様にすると今度もACCESSと同じ用に更新処理されていました。SQLSERVERのことを知らないのでアップサイジングで処理すればいいものと思い今回の騒動になってしまいました。今回の丁寧なご回答有難うございました。たいへん役に立ちましたこれからもへんな投稿をするかもしれませんが宜しくお願いします。本当に有難うございました、又、貴重な時間を裂いて頂き申し訳ございませんでした。

回答No.3

jamshid6

以下のような構造で試しましたが、再現しません。
SQL ExpressはSP2あたっているみたいですし、Accessは2000でも再現しませんでした。

CREATE TABLE [dbo].[TBLA](
[KEY1] [int] NOT NULL,
[KEY2] [int] NOT NULL)
GO
ALTER TABLE TBLA ADD CONSTRAINT [PKA] PRIMARY KEY CLUSTERED (KEY1,KEY2)
GO
CREATE TABLE [dbo].[TBLB](
[KEY1] [int] NOT NULL,
[KEY2] [int] NOT NULL,
[KEY3] [nvarchar](6) NOT NULL)
GO
ALTER TABLE TBLB ADD CONSTRAINT [PKB] PRIMARY KEY CLUSTERED (KEY1,KEY2,KEY3)
GO
CREATE TABLE [dbo].[TBLC](
[KEY1] [int] NOT NULL,
[KEY2] [int] NOT NULL,
[KEY3] [nvarchar](6) NOT NULL,
[KEY4] [nvarchar](15) NOT NULL)
GO
ALTER TABLE TBLC ADD CONSTRAINT [PKC] PRIMARY KEY CLUSTERED (KEY1,KEY2,KEY3,KEY4)
GO
CREATE TABLE [dbo].[TBLD](
[KEY1] [int] NOT NULL,
[KEY2] [int] NOT NULL,
[KEY3] [nvarchar](6) NOT NULL,
[KEY4] [nvarchar](15) NOT NULL,
[KEY5] [nvarchar](6) NOT NULL)
GO
ALTER TABLE TBLD ADD CONSTRAINT [PKD] PRIMARY KEY CLUSTERED (KEY1,KEY2,KEY3,KEY4,KEY5)
GO
ALTER TABLE TBLB ADD CONSTRAINT [FKB] FOREIGN KEY (KEY1,KEY2) REFERENCES TBLA (KEY1,KEY2) ON UPDATE CASCADE
GO
ALTER TABLE TBLC ADD CONSTRAINT [FKC] FOREIGN KEY (KEY1,KEY2,KEY3) REFERENCES TBLB (KEY1,KEY2,KEY3) ON UPDATE CASCADE
GO
ALTER TABLE TBLD ADD CONSTRAINT [FKD] FOREIGN KEY (KEY1,KEY2,KEY3,KEY4) REFERENCES TBLC (KEY1,KEY2,KEY3,KEY4) ON UPDATE CASCADE
GO

こうなると、どうも外部キーがうまくかかっていないとしか思えないのですが、そこを確認していただきたいです。
外部キーを右クリックして「名前をつけてキーをスクリプト化」を選ぶと、CREATEとDROPしか選べませんが、CREATEの方のスクリプトを生成して中身が実質上のようになっているかどうか確認してみてください（WITH CHECKとかCHECK CONSTRAINTも書き出されますがそこは無視して結構です）。

なお、924012はSSAS(Analysis Service)に関するものですので、今回のとは関係ないと思います。

お礼

いつも有難うございます。
外部KEYのスクリプト化でCREATEで出来たデータをとりましたが内容が違うので動かないのでしょうね。取りあえず下記に貼り付けましたので宜しくお願いします。尚、教えて頂いた部分によく似たスクリプトはテーブルを右クリックし名前を付けてスクリプト化すると表示されていました。

(ＴＢＬＡの外部キー部分）
外部キーはありません。

(ＴＢＬＢの外部キー部分）
USE [sqltesSQL]
GO
ALTER TABLE [dbo].[TBLB] WITH NOCHECK ADD CONSTRAINT [TBLB_FK00] FOREIGN KEY([key1], [key2])
REFERENCES [dbo].[TBLA] ([key1], [key2])
GO
ALTER TABLE [dbo].[TBLB] NOCHECK CONSTRAINT [TBLB_FK00]

(ＴＢＬＣの外部キー部分）
USE [sqltesSQL]
GO
ALTER TABLE [dbo].[TBLC] WITH NOCHECK ADD CONSTRAINT [TBLC_FK00] FOREIGN KEY([key1], [key2], [key3])
REFERENCES [dbo].[TBLB] ([key1], [key2], [key3])
GO
ALTER TABLE [dbo].[TBLC] NOCHECK CONSTRAINT [TBLC_FK00]

(ＴＢＬＤの外部キー部分）
USE [sqltesSQL]
GO
ALTER TABLE [dbo].[TBLD] WITH NOCHECK ADD CONSTRAINT [TBLD_FK00] FOREIGN KEY([key1], [key2], [key3], [key4])
REFERENCES [dbo].[TBLC] ([key1], [key2], [key3], [key4])
GO
ALTER TABLE [dbo].[TBLD] NOCHECK CONSTRAINT [TBLD_FK00]

補足

毎回ご返事ありがとうございます。
職場でしか環境がないので申し訳ございませんが休み明けに確認したいと思います。又、報告させて頂きますので最後までお付き合い願います。本当にありがとうございます。

回答No.2

jamshid6

正直な話、再現できません（再現したらDBMSとして致命的な話だと思いますが）。
1) 先に書かれたTBL1,TBL2という仮定のテーブルでも、同じ事象が起こりますか？
　・その場合のTBL1,TBL2の主キーはそれぞれ、KEY1,KEY2,KEY3とKEY1,KEY2,KEY3,KEY4ですか？
　・TBL2に掛けられている外部キーの定義を教えてください
　　（SQL Server Management Studio Expressはお持ちですよね？
　　　テーブルの下にキーというフォルダがあり、その中の灰色のキーが外部キーですが、
　　　右クリックするとスクリプトが生成できます）
　・直接以下のクエリを実行して、同じエラーが出るか教えてください。
　　UPDATE TBL1 SET KEY1=3
2) 最初に製品版にはパッチが出ていると書かれていましたが、その情報を今もお持ちですか？
3) AccessのバージョンとSQL Server Expressのバージョンを教えてください（9.00.xxxx.00とかオブジェクトエクスプローラに表示されるはずです）

補足

ご返事遅くなり申し訳ございません。
少し前回お返事した内容に変更・追加がございまして、今回の回答頂きましてテストした結果を合わせて報告させて頂きます。実は今回のエラーを起こしたテーブルの関係が4つのテーブルからなっています。TBL-A(KEY1,KEY2)・TBL-B(KEY1,KEY2,KEY3)・TBL-C(KEY1,KEY2,KEY3,KEY4)・TBL-D(KEY1,KEY2,KEY3,KEY4,KEY5)があります。最初の質問させて頂いた分は、TBL-CとTBL-Dの関係で質問させて頂きましたが、今回テストさせて頂いて判ったのは、TBL-AのKEY1を変更しますとACCESSではすべて関係するデータ（TBL-A～D)は更新されますが、SQLSERVERの場合TBL-A,TBL-Bは変更されましたがTBL-CとTBL-Dはうまく更新されない様です。内容は
TBL-A(1,2)、
TBL-B(1行目1,2,3 2行目1,2,4)、
TBL-C(1行目1,2,3,4 2行目1,2,3,5 3行目1,2,4,1)、
TBL-D(1行目1,2,3,4,AA 2行目1,2,3,4,BB 3行目1,2,3,4,CC)に設定しTBL-AのKEY1を500に変更しました。結果は
TBL-A(500,2)、
TBL-B(1行目500,2,3 2行目500,2,4)、
TBL-C(1行目500,2,4,4 2行目500,2,4,5 3行目500,2,4,1)、
TBL-D(1行目500,2,4,1,AA 2行目500,2,4,1,BB 3行目500,2,4,1,CC)となりました。又、TBL-Dに4行目1,2,3,5,AA 5行目1,2,3,5,BBを入れて置くと重複したKEYは挿入できませんというメッセージが表示されます。ACCESSではうまくいく処理がSQLSERVERでは1対1の更新はうまく行きますが、複数行の1対多の場合ACCESSと処理が違うようでした。私の勘違いで最後のTBL-CとTBL-Dの関係だけを取り上げ質問しましたのでややこしくなったと思いますが宜しくご教授願います。
追伸、今回回答の返答は１－KEY1～5はすべて主KEYとしています。又、UPDATEはグレーアウトしていてCREATとDROPとDROP及びCREAT TO(R)しか触れません。定義はKEY1-INT(NULL許可しない)、KEY2-INT(NULL許可しない)、KEY3-NVARCHAR(6)(NULL許可しない)、KEY4-NVARCHAR(15)(NULL許可しない)、KEY5-NVARCHAR(6)(NULL許可しない)。２－今回のエラーとは関係ないかもしれませんが文書番号924012に4つ以上のKEYを含む・・・がありこれかと思っていました。３－ACCESSは2002のSP3です。SQLSERVERは2005 EXPRESS EDITION 9.00.3042.00です。誠に申し訳ございませんが宜しくお願い致します。