UNIQUE constraint exception thrown on empty table INSERT [sql-server]

Question

My INSERT statement fails while it is trying to add a new record into an empty table (Attribute) (no record yet). I am surprised by the error raised by the system:

Violation of UNIQUE KEY constraint 'CK_Attribute_Name_IDproject'. Cannot insert duplicate key in object 'dbo.Attribute'. The duplicate key value is (dummy, 55).

The creation script for this table looks like

CREATE TABLE [dbo].[Attribute](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IDproject] [int] NOT NULL,
    [IDtype] [int] NOT NULL,
    [IDgroup] [int] NOT NULL,
    [name] [varchar](50) NOT NULL,
    [color] [int] NULL,
    [protected] [tinyint] NULL,
    [datemodified] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [CK_Attribute_Name_IDproject] UNIQUE NONCLUSTERED 
(
    [name] ASC,
    [IDproject] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

I skiped foreign keys references and default values which does not seem of interest in this context. The UNIQUE constraint applies to [name] and [IDproject].

When running the following statement

SELECT *
  FROM [dbo].[Attribute]
GO
SELECT *
  FROM [dbo].[Project]
GO

I get the results

(0 row(s) affected) (2 row(s) affected)

The first result indicats the Attribute Table is empty The second that there are 2 Projects

then running the following INSERT in table Attribute it failed with the above mentioned UNIQUE CONSTRAINT error

INSERT INTO [dbo].[Attribute] ([IDproject], [name], [IDtype], [IDgroup], [color], [protected], [datemodified])
        SELECT DISTINCT
            p.[ID],'dummy',t.[ID],g.[ID],-1,0,getdate()
        FROM [dbo].[Project] p
        INNER JOIN [dbo].[Group] g ON g.[name]='none' AND g.[IDproject] = p.[ID]
        INNER JOIN [dbo].[AttributeType] t ON t.[format]='text' AND g.[IDproject] = p.[ID]
        WHERE p.[name]='TESTPROJ'
GO

How can i get such an error on an empty table ?

Only way is that the query you're using is producing duplicate key, in this case name is set to 'dummy' on all rows, so if same p.ID is on multiple rows it will result in duplicate key error — MWillemse
Your table "Attribute" have the foreign key relationship with Project table. So if you try to insert any value in the Attribute table it should be in the ** Project** table. In your case seems like you are inserting IDproject value which does not exist in Project table. So make sure that IDproject value is the value which is present in the Project table. Let me know if you want further clarification — Shyam Sa
OK I got it. the derived SELECT returns 2 records with 'dummy' due to a duplicate INTO one of table, AttributeType, with which INNER JOIN is performed. — pascal sautot
Post an answer to your own question so it stops showing up in the "unanswered" search please — Caius Jard
You make the field [name] unique, then you hard coded it to 'dummy' on insert. Of course it will error out if there are more than 1 row insertion because the second row will also have the value 'dummy'. — Eric

pascal sautot pascal sautot · Accepted Answer · 2017-08-01T14:27:07

I have found the solution myself: the derived SELECT returns 2 records with 'dummy' due to a duplicate INTO one of table, AttributeType, with which INNER JOIN is performed.

UNIQUE constraint exception thrown on empty table INSERT [sql-server]

1 Answers