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

• Microsoft SQL Server, versions 6.0 and 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