Knowledge Base

BUG: AV When INSERT SELECT UNION into a Table with a Foreign Key

Article ID: 166201

Article Last Modified on 10/17/2003

APPLIES TO

This article was previously published under Q166201
BUG #: 16712

SYMPTOMS

Using an INSERT INTO SELECT UNION statement to a table with a foreign key constraint may cause an access violation (AV). The following script demonstrates the problem: 

   CREATE TABLE dog
   (
      a INT,
      b INT
   )
   GO

   ALTER TABLE dog
      ADD CONSTRAINT PKDog PRIMARY KEY (a)
   GO


   CREATE TABLE cat
   (
      b INT,
      c char(1)
   )
   GO

   ALTER TABLE cat
      ADD CONSTRAINT PKCat PRIMARY KEY (b)
   GO

   ALTER TABLE dog
      ADD CONSTRAINT FKCat_Dog FOREIGN KEY (b)
      REFERENCES cat
   GO

   INSERT INTO cat SELECT 1, 'a'
   GO

   INSERT INTO dog (a, b)
      SELECT 1,1
      UNION
      SELECT 2,1
   GO


On the client side, the application will receive the following error:

DB-Library Process Dead - Connection Broken

WORKAROUND

To work around this problem, create a temporary table to hold the result set from the UNION statement, then INSERT INTO a target table by selecting data from temporary table. The following script demonstrates the workaround for the above scenario: 

   CREATE TABLE #temp
   (
      a INT,
      b INT
   )
   GO

   INSERT INTO #temp
      SELECT 1,1
      UNION
      SELECT 2,1
   GO

   INSERT INTO dog (a, b)
      SELECT * FROM #temp

STATUS

Microsoft has confirmed this to be a problem in SQL Server 6.0 and 6.5.

Keywords: kbbug kbpending kbusage KB166201


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.