473,544 Members | 1,939 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

[SQLServer] Violation of UNIQUE KEY constraint

2 New Member
[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.

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
2 15048
rorajoey
2 New Member
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
2,878 Recognized Expert Specialist
Sometimes we only need to look at it twice, again :)

Happy coding!

-- CK
Jan 20 '09 #3

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

Similar topics

3
11185
by: KULJEET | last post by:
foreign key also refer to unique constraint. (GREAT...) 1.then table that containt unique constraint act as master table????? 2.IS unique constraint will replace with primary key?? 3.Is unique constraint gives all fuctionality as primary key constraint??? thanks kuljeet pal singh
26
45379
by: Agoston Bejo | last post by:
I want to enforce such a constraint on a column that would ensure that the values be all unique, but this wouldn't apply to NULL values. (I.e. there may be more than one NULL value in the column.) How can I achieve this? I suppose I would get the most-hated "table/view is changing, trigger/function may not see it" error if I tried to write a...
2
3884
by: Mansoor Azam | last post by:
When I add a unique key constraint to column in SQL 6.5 why does it also create an index. e.g. In the table subaccounts I added a unique key constraint for the column login and SQL creates an index with the name UQ_SubAccounts_2__19 (UKC). Does this also mean that there is no need to create an index for this column? thx
5
10849
by: Kamil | last post by:
Hello What should I use for better perfomance since unique constraint always use index ? Thanks Kamil
5
16692
by: aj | last post by:
DB2 WSE 8.1 FP5 Red Hat AS 2.1 What is the difference between adding a unique constraint like: ALTER TABLE <SCHEMA>.<TABLE> ADD CONSTRAINT CC1131378283225 UNIQUE ( <COL1>) ; and adding a unique index like:
7
6653
by: Brian Keating | last post by:
Hi there, Is it possible to add a unique constraint on two columns in a table, so that the constraint is a composite of the two? i.e. these two columns together should be unique...? i.e. column1 column2 1 1 1 2
2
6533
by: D. Dante Lorenso | last post by:
I'm trying to build a table that will store a history of records by enumerating the records. I want the newest record to always be number ZERO, so I created a trigger on my table to handle the assignment of version numbers: CREATE TRIGGER "trg_audio_file_insert" BEFORE INSERT ON "public"."audio_file" FOR EACH ROW EXECUTE PROCEDURE...
10
14654
by: Laurence | last post by:
Hi there, How to differentiate between unique constraint and unique index? These are very similar but I cannot differentiate them? Could someone give me a hand? Thanks in advance
3
4299
by: trinanjan | last post by:
Why Unique Constraint Violation Takes Place In Oracle? How This Problen Can Be Solved?
0
7420
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7362
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7602
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7764
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7368
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
4911
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3407
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1837
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
983
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.