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

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

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

SYMPTOMS

WORKAROUND

STATUS