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

ID: Q166201

The information in this article applies to:

Microsoft SQL Server versions 6.0, 6.5

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 version 6.0 and 6.5.

Additional query words:

Keywords : kbusage SSrvTran_SQL kbbug6.50 kbbug6.00
Version : winnt:6.0,6.5
Platform : winnt
Issue type : kbbug