[SQLServer] Violation of UNIQUE KEY constraint

[rorajoey]

[SQLServer]Violation of UNIQUE KEY constraint 'IX_surveyQuest ions'. Cannot insert duplicate key in object 'dbo.surveyQues tions'.

This might seem like a simple matter of trying to insert a row with ID=20 when there's already one with that ID, but the problem is a bit more complicated. The table is supposed to auto-increment the value for the primary key when a new record is inserted. But no matter what I do, I can't seem to insert more than one record into this table.

USE [opinion8]
GO
/****** Object:  Table [dbo].[surveyQuestions]    Script Date: 01/20/2009 00:00:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[surveyQuestions](
	[surveyQuestionID] [int] IDENTITY(1,1) NOT NULL,
	[surveyQuestionTypeID] [smallint] NOT NULL,
	[created] [datetime] NOT NULL CONSTRAINT [DF__surveyQue__creat__656C112C]  DEFAULT (getdate()),
	[approvedFlag] [bit] NOT NULL,
	[activeFlag] [bit] NOT NULL,
	[surveyPageID] [int] NOT NULL,
	[displayOrder] [smallint] NOT NULL,
	[requiredFlag] [bit] NOT NULL,
	[parentQuestionID] [int] NOT NULL CONSTRAINT [DF__surveyQue__paren__6754599E]  DEFAULT ((0)),
	[clientID] [int] NOT NULL,
	[surveyID] [int] NOT NULL,
	[question] [varchar](5000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_surveyQuestions_question]  DEFAULT ('[none]'),
	[instructions] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_surveyQuestions_instructions]  DEFAULT ('[none]'),
	[requiredText] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_surveyQuestions_requiredText]  DEFAULT ('[none]'),
 CONSTRAINT [PK_surveyQuestions] PRIMARY KEY CLUSTERED 
(
	[surveyQuestionID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_surveyQuestions] UNIQUE NONCLUSTERED 
(
	[parentQuestionID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[surveyQuestions]  WITH CHECK ADD  CONSTRAINT [FK_surveyQuestions_clients] FOREIGN KEY([clientID])
REFERENCES [dbo].[clients] ([clientID])
GO
ALTER TABLE [dbo].[surveyQuestions] CHECK CONSTRAINT [FK_surveyQuestions_clients]
GO
ALTER TABLE [dbo].[surveyQuestions]  WITH CHECK ADD  CONSTRAINT [FK_surveyQuestions_surveyPages] FOREIGN KEY([surveyPageID])
REFERENCES [dbo].[surveyPages] ([surveyPageID])
GO
ALTER TABLE [dbo].[surveyQuestions] CHECK CONSTRAINT [FK_surveyQuestions_surveyPages]
GO
ALTER TABLE [dbo].[surveyQuestions]  WITH CHECK ADD  CONSTRAINT [FK_surveyQuestions_surveyPages1] FOREIGN KEY([surveyPageID])
REFERENCES [dbo].[surveyPages] ([surveyPageID])
GO
ALTER TABLE [dbo].[surveyQuestions] CHECK CONSTRAINT [FK_surveyQuestions_surveyPages1]
GO
ALTER TABLE [dbo].[surveyQuestions]  WITH CHECK ADD  CONSTRAINT [FK_surveyQuestions_surveys] FOREIGN KEY([surveyID])
REFERENCES [dbo].[surveys] ([surveyID])
GO
ALTER TABLE [dbo].[surveyQuestions] CHECK CONSTRAINT [FK_surveyQuestions_surveys]

Here's the ColdFusion code I'm using to insert the record:

        <cftransaction>
            <cfquery name="qInsertQuestion" datasource="#application.DSN#">
                insert into surveyQuestions (
                    question,
                    instructions,
                    surveyQuestionTypeID,
                    approvedFlag,
                    activeFlag,
                    surveyPageID,
                    displayOrder,
                    requiredFlag,
                    requiredText,
                    parentQuestionID,
                    clientID,
                    surveyID,
                    created
                ) values (
                    <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="5000" value="#left(arguments.oQuestion.getValue('question',true),5000)#">,
                    <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="500" value="#left(arguments.oQuestion.getValue('instructions',true),500)#">,
                    <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('surveyQuestionTypeID',true)#">,
                    <cfqueryparam cfsqltype="cf_sql_bit" value="#arguments.oQuestion.getValue('approvedFlag',true)#">,
                    <cfqueryparam cfsqltype="cf_sql_bit" value="#arguments.oQuestion.getValue('activeFlag',true)#">,
                    <cfqueryparam cfsqltype="cf_sql_smallint" value="#arguments.oQuestion.getValue('surveyPageID',true)#">,
                    <cfqueryparam cfsqltype="cf_sql_smallint" value="#arguments.oQuestion.getValue('displayOrder',true)#">,
                    <cfqueryparam cfsqltype="cf_sql_bit" value="#arguments.oQuestion.getValue('requiredFlag',true)#">,
                    <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="200" value="#left(arguments.oQuestion.getValue('requiredText',true),200)#">,
                    <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('parentQuestionID',true)#">,
                    <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('clientID',true)#">,
                    <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('surveyID',true)#">,
                    <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
                )
                select @@identity as questionID
            </cfquery>
		<cfset newID = qInsertQuestion.questionID>
            <cfquery name="qInsertQuestionModule" datasource="#application.DSN#">
            	insert into surveyQuestionsTextbox (
                	surveyQuestionID,
                    maxLength,
                    fieldSize,
                    formatHint,
                    pickType,
                    clientID
                ) values (
                    <cfqueryparam cfsqltype="cf_sql_integer" value="#newID#">,
                    <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('maxLength',true)#">,
                    <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('fieldSize',true)#">,
                    <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="100" value="#arguments.oQuestion.getValue('formatHint',true)#">,
                    <cfqueryparam cfsqltype="cf_sql_char" maxlength="2" value="#arguments.oQuestion.getValue('pickType',true)#">,
                    <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('clientID',true)#">
                )
            </cfquery>
        </cftransaction>

I'm trying to insert data into 2 tables within this transaction. The error points at the first table, but I know that error messages often don't tell you what the real problem is. Here's the other table:

USE [opinion8]
GO
/****** Object:  Table [dbo].[surveyQuestionsTextbox]    Script Date: 01/20/2009 00:02:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[surveyQuestionsTextbox](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[formatHint] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_surveyQuestionsTextbox_formatHint]  DEFAULT ('[none]'),
	[fieldSize] [int] NOT NULL CONSTRAINT [DF_surveyQuestionsTextbox_fieldSize]  DEFAULT ((40)),
	[maxLength] [int] NOT NULL CONSTRAINT [DF_surveyQuestionsTextbox_maxLength]  DEFAULT ((200)),
	[pickType] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_surveyQuestionsTextbox_pickType]  DEFAULT ('RP'),
	[clientID] [int] NOT NULL,
	[surveyQuestionID] [int] NOT NULL,
 CONSTRAINT [PK_surveyQuestionsTextbox] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_surveyQuestionsTextbox] UNIQUE NONCLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[surveyQuestionsTextbox]  WITH CHECK ADD  CONSTRAINT [FK_surveyQuestionsTextbox_clients] FOREIGN KEY([clientID])
REFERENCES [dbo].[clients] ([clientID])
GO
ALTER TABLE [dbo].[surveyQuestionsTextbox] CHECK CONSTRAINT [FK_surveyQuestionsTextbox_clients]
GO
ALTER TABLE [dbo].[surveyQuestionsTextbox]  WITH CHECK ADD  CONSTRAINT [FK_surveyQuestionsTextbox_surveyQuestions] FOREIGN KEY([surveyQuestionID])
REFERENCES [dbo].[surveyQuestions] ([surveyQuestionID])
GO
ALTER TABLE [dbo].[surveyQuestionsTextbox] CHECK CONSTRAINT [FK_surveyQuestionsTextbox_surveyQuestions]

I'm extremely perplexed, because my common sense tells me that if a primary key is automatically incremented, there should be no conflicts. (It also tells me that I can't simultaneously have tea and no tea, so perhaps it's not entirely reliable.) Any useful clues would be appreciated.

[rorajoey]

Found the problem.

As it turns out, the index was set on a column other than the primary key column. I don't see how that could have happened, so I'm still perplexed (and inclined to blame Microsoft SQL Server Management Studio), but at least I can move forward with this project.

[ck9663]

Sometimes we only need to look at it twice, again :)

Happy coding!

-- CK