Information générale
Forum:
Microsoft SQL Server
Titre:
Referential integrity bug??
If i create the tables Parent and Child as follows :
------------------------------------------------------------------------
CREATE TABLE [dbo].[Child] (
[ParentID] [int] NOT NULL ,
[ChildID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Parent] (
[ParentID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Child] WITH NOCHECK ADD
CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED
(
[ChildID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Parent] WITH NOCHECK ADD
CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED
(
[ParentID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Child] ADD
CONSTRAINT [FK_Child_Parent] FOREIGN KEY
(
[ParentID]
) REFERENCES [dbo].[Parent] (
[ParentID]
)
GO
----------------------------------------------------------------------------------
Notice, there is a foreign key constraint between the Parent and Child table based on the field ParentID.
Now add a few sample records :
---------------------------------------------------------------------------------
INSERT INTO Parent VALUES (1)
INSERT INTO Parent VALUES (2)
INSERT INTO Parent VALUES (3)
INSERT INTO Child VALUE(1,1)
INSERT INTO Child VALUE(1,2)
INSERT INTO Child VALUE(2,3)
----------------------------------------------------------------------------------
If you now try to run the following code in query analyzer, it will fail as appriate, due to the foreign key constraint. ( there isn't any Parent with ParentID of 6 ).
----------------------------------------------------------------------------------
declare @newid int
declare @oldid int
set @newid = 2
set @oldid = 2
UPDATE child
SET parentid = 6, childid = @newid
WHERE childid = @oldid
------------------------------------------------------------------------------------
However, if you run similar code in Visual FoxPro, SQL server will accept the bad ParentID.
------------------------------------------------------------------------------------
newid = 2
oldid = 2
cStr = "update child set parentid = 6, childid = ?newid where childid = ?oldid"
?SQLEXEC(nConn,cStr)
------------------------------------------------------------------------------------
But this is only in the case of using parameters. If you set cStr as :
cStr = "update child set parentid = 6, childid = 2 where childid = 2"
it fails apppriately.
Could someone confirm this and tell me I'm not going crazy?
TIA,
Anthony Letts
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement