Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Referential integrity bug??
Message
De
24/01/2001 12:28:23
 
 
À
Tous
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Autre
Titre:
Referential integrity bug??
Divers
Thread ID:
00467998
Message ID:
00467998
Vues:
52
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
Fil
Voir

Click here to load this message in the networking platform