Microsoft KB Archive/256307
Article ID: 256307
Article Last Modified on 3/14/2006
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q256307
BUG #: 53833 (SQLBUG_70)
When you update a table that has a composite foreign key, and one of the columns in the foreign key constraint is NULL, the update fails with this error:
As stated in SQL Server Books Online, you should skip the verification of the FOREIGN KEY constraint.
Here are two ways to work around this:
- Define all the columns used in a foreign key constraint as NOT NULL.
- Update all the columns used in a foreign key constraint in the same update statement.
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.
Here is a script that reproduces the error:
CREATE TABLE Pk( Col1 VARCHAR ( 25 ) NOT NULL, Col2 VARCHAR ( 25 ) NOT NULL, CONSTRAINT Pk1 PRIMARY KEY (Col1, Col2) ) GO CREATE TABLE Fk ( Pk1 VARCHAR ( 25 ) NOT NULL, Col1 VARCHAR ( 25 ) NOT NULL, Col2 VARCHAR ( 25 ) NULL, Col3 VARCHAR ( 25 ) NULL, CONSTRAINT FkPk1 PRIMARY KEY (Pk1) ) GO ALTER TABLE Fk ADD CONSTRAINT Fk1 FOREIGN KEY (Col1, Col2 ) REFERENCES Pk(Col1, Col2 ) GO INSERT INTO Pk(Col1, Col2) VALUES ('ABC', 'Test' ) GO INSERT INTO Fk (Pk1, Col1, Col2, Col3) VALUES ('ONE', 'ABC', NULL, NULL) GO UPDATE Fk SET Col1 = 'ABC' WHERE Pk1 = 'ONE' GO
The preceding update does not fail if the second column is also updated to NULL or a valid value; nor does it fail when the second field is not NULL (just update col1 when col2 is not null). However, this code works:
UPDATE LeeMacFk SET Col1 = 'ABC', col2=NULL WHERE Pk1 = 'ONE' go --> works fine UPDATE LeeMacFk SET Col2 = 'Test' WHERE Pk1 = 'ONE' go --> works fine UPDATE LeeMacFk SET Col1 = 'ABC' WHERE Pk1 = 'ONE' go --> works fine..
The update fails only when one of the composite foreign key columns is NULL.
Additional query words: update foreign key
Keywords: kbbug kbfix KB256307