By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,490 Members | 1,398 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,490 IT Pros & Developers. It's quick & easy.

[SQLServer] Violation of UNIQUE KEY constraint

P: 2
[SQLServer]Violation of UNIQUE KEY constraint 'IX_surveyQuestions'. Cannot insert duplicate key in object 'dbo.surveyQuestions'.

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.

Expand|Select|Wrap|Line Numbers
  1. USE [opinion8]
  2. GO
  3. /****** Object:  Table [dbo].[surveyQuestions]    Script Date: 01/20/2009 00:00:09 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. SET ANSI_PADDING ON
  9. GO
  10. CREATE TABLE [dbo].[surveyQuestions](
  11.     [surveyQuestionID] [int] IDENTITY(1,1) NOT NULL,
  12.     [surveyQuestionTypeID] [smallint] NOT NULL,
  13.     [created] [datetime] NOT NULL CONSTRAINT [DF__surveyQue__creat__656C112C]  DEFAULT (getdate()),
  14.     [approvedFlag] [bit] NOT NULL,
  15.     [activeFlag] [bit] NOT NULL,
  16.     [surveyPageID] [int] NOT NULL,
  17.     [displayOrder] [smallint] NOT NULL,
  18.     [requiredFlag] [bit] NOT NULL,
  19.     [parentQuestionID] [int] NOT NULL CONSTRAINT [DF__surveyQue__paren__6754599E]  DEFAULT ((0)),
  20.     [clientID] [int] NOT NULL,
  21.     [surveyID] [int] NOT NULL,
  22.     [question] [varchar](5000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_surveyQuestions_question]  DEFAULT ('[none]'),
  23.     [instructions] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_surveyQuestions_instructions]  DEFAULT ('[none]'),
  24.     [requiredText] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_surveyQuestions_requiredText]  DEFAULT ('[none]'),
  25.  CONSTRAINT [PK_surveyQuestions] PRIMARY KEY CLUSTERED 
  26. (
  27.     [surveyQuestionID] ASC
  28. )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
  29.  CONSTRAINT [IX_surveyQuestions] UNIQUE NONCLUSTERED 
  30. (
  31.     [parentQuestionID] ASC
  32. )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
  33. ) ON [PRIMARY]
  34.  
  35. GO
  36. SET ANSI_PADDING OFF
  37. GO
  38. ALTER TABLE [dbo].[surveyQuestions]  WITH CHECK ADD  CONSTRAINT [FK_surveyQuestions_clients] FOREIGN KEY([clientID])
  39. REFERENCES [dbo].[clients] ([clientID])
  40. GO
  41. ALTER TABLE [dbo].[surveyQuestions] CHECK CONSTRAINT [FK_surveyQuestions_clients]
  42. GO
  43. ALTER TABLE [dbo].[surveyQuestions]  WITH CHECK ADD  CONSTRAINT [FK_surveyQuestions_surveyPages] FOREIGN KEY([surveyPageID])
  44. REFERENCES [dbo].[surveyPages] ([surveyPageID])
  45. GO
  46. ALTER TABLE [dbo].[surveyQuestions] CHECK CONSTRAINT [FK_surveyQuestions_surveyPages]
  47. GO
  48. ALTER TABLE [dbo].[surveyQuestions]  WITH CHECK ADD  CONSTRAINT [FK_surveyQuestions_surveyPages1] FOREIGN KEY([surveyPageID])
  49. REFERENCES [dbo].[surveyPages] ([surveyPageID])
  50. GO
  51. ALTER TABLE [dbo].[surveyQuestions] CHECK CONSTRAINT [FK_surveyQuestions_surveyPages1]
  52. GO
  53. ALTER TABLE [dbo].[surveyQuestions]  WITH CHECK ADD  CONSTRAINT [FK_surveyQuestions_surveys] FOREIGN KEY([surveyID])
  54. REFERENCES [dbo].[surveys] ([surveyID])
  55. GO
  56. ALTER TABLE [dbo].[surveyQuestions] CHECK CONSTRAINT [FK_surveyQuestions_surveys]
  57.  
Here's the ColdFusion code I'm using to insert the record:

Expand|Select|Wrap|Line Numbers
  1.         <cftransaction>
  2.             <cfquery name="qInsertQuestion" datasource="#application.DSN#">
  3.                 insert into surveyQuestions (
  4.                     question,
  5.                     instructions,
  6.                     surveyQuestionTypeID,
  7.                     approvedFlag,
  8.                     activeFlag,
  9.                     surveyPageID,
  10.                     displayOrder,
  11.                     requiredFlag,
  12.                     requiredText,
  13.                     parentQuestionID,
  14.                     clientID,
  15.                     surveyID,
  16.                     created
  17.                 ) values (
  18.                     <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="5000" value="#left(arguments.oQuestion.getValue('question',true),5000)#">,
  19.                     <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="500" value="#left(arguments.oQuestion.getValue('instructions',true),500)#">,
  20.                     <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('surveyQuestionTypeID',true)#">,
  21.                     <cfqueryparam cfsqltype="cf_sql_bit" value="#arguments.oQuestion.getValue('approvedFlag',true)#">,
  22.                     <cfqueryparam cfsqltype="cf_sql_bit" value="#arguments.oQuestion.getValue('activeFlag',true)#">,
  23.                     <cfqueryparam cfsqltype="cf_sql_smallint" value="#arguments.oQuestion.getValue('surveyPageID',true)#">,
  24.                     <cfqueryparam cfsqltype="cf_sql_smallint" value="#arguments.oQuestion.getValue('displayOrder',true)#">,
  25.                     <cfqueryparam cfsqltype="cf_sql_bit" value="#arguments.oQuestion.getValue('requiredFlag',true)#">,
  26.                     <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="200" value="#left(arguments.oQuestion.getValue('requiredText',true),200)#">,
  27.                     <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('parentQuestionID',true)#">,
  28.                     <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('clientID',true)#">,
  29.                     <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('surveyID',true)#">,
  30.                     <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
  31.                 )
  32.                 select @@identity as questionID
  33.             </cfquery>
  34.         <cfset newID = qInsertQuestion.questionID>
  35.             <cfquery name="qInsertQuestionModule" datasource="#application.DSN#">
  36.                 insert into surveyQuestionsTextbox (
  37.                     surveyQuestionID,
  38.                     maxLength,
  39.                     fieldSize,
  40.                     formatHint,
  41.                     pickType,
  42.                     clientID
  43.                 ) values (
  44.                     <cfqueryparam cfsqltype="cf_sql_integer" value="#newID#">,
  45.                     <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('maxLength',true)#">,
  46.                     <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('fieldSize',true)#">,
  47.                     <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="100" value="#arguments.oQuestion.getValue('formatHint',true)#">,
  48.                     <cfqueryparam cfsqltype="cf_sql_char" maxlength="2" value="#arguments.oQuestion.getValue('pickType',true)#">,
  49.                     <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.oQuestion.getValue('clientID',true)#">
  50.                 )
  51.             </cfquery>
  52.         </cftransaction>
  53.  
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:

Expand|Select|Wrap|Line Numbers
  1. USE [opinion8]
  2. GO
  3. /****** Object:  Table [dbo].[surveyQuestionsTextbox]    Script Date: 01/20/2009 00:02:57 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. SET ANSI_PADDING ON
  9. GO
  10. CREATE TABLE [dbo].[surveyQuestionsTextbox](
  11.     [id] [int] IDENTITY(1,1) NOT NULL,
  12.     [formatHint] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_surveyQuestionsTextbox_formatHint]  DEFAULT ('[none]'),
  13.     [fieldSize] [int] NOT NULL CONSTRAINT [DF_surveyQuestionsTextbox_fieldSize]  DEFAULT ((40)),
  14.     [maxLength] [int] NOT NULL CONSTRAINT [DF_surveyQuestionsTextbox_maxLength]  DEFAULT ((200)),
  15.     [pickType] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_surveyQuestionsTextbox_pickType]  DEFAULT ('RP'),
  16.     [clientID] [int] NOT NULL,
  17.     [surveyQuestionID] [int] NOT NULL,
  18.  CONSTRAINT [PK_surveyQuestionsTextbox] PRIMARY KEY CLUSTERED 
  19. (
  20.     [id] ASC
  21. )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
  22.  CONSTRAINT [IX_surveyQuestionsTextbox] UNIQUE NONCLUSTERED 
  23. (
  24.     [id] ASC
  25. )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
  26. ) ON [PRIMARY]
  27.  
  28. GO
  29. SET ANSI_PADDING OFF
  30. GO
  31. ALTER TABLE [dbo].[surveyQuestionsTextbox]  WITH CHECK ADD  CONSTRAINT [FK_surveyQuestionsTextbox_clients] FOREIGN KEY([clientID])
  32. REFERENCES [dbo].[clients] ([clientID])
  33. GO
  34. ALTER TABLE [dbo].[surveyQuestionsTextbox] CHECK CONSTRAINT [FK_surveyQuestionsTextbox_clients]
  35. GO
  36. ALTER TABLE [dbo].[surveyQuestionsTextbox]  WITH CHECK ADD  CONSTRAINT [FK_surveyQuestionsTextbox_surveyQuestions] FOREIGN KEY([surveyQuestionID])
  37. REFERENCES [dbo].[surveyQuestions] ([surveyQuestionID])
  38. GO
  39. ALTER TABLE [dbo].[surveyQuestionsTextbox] CHECK CONSTRAINT [FK_surveyQuestionsTextbox_surveyQuestions]
  40.  
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.
Jan 20 '09 #1
Share this Question
Share on Google+
2 Replies


P: 2
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.
Jan 20 '09 #2

ck9663
Expert 2.5K+
P: 2,878
Sometimes we only need to look at it twice, again :)

Happy coding!

-- CK
Jan 20 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.