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

• Microsoft SQL Server versions 6.0, 6.5

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 

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 

Last Reviewed: February 10, 1999