General information
Forum:
Microsoft SQL Server
Title:
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
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only