?
Solved

Violation of UNIQUE KEY constraint 'IX_VACANCY_UNIQUE'. Cannot insert duplicate key in object 'dbo.VACANCY'.

Posted on 2011-05-10
7
Medium Priority
?
788 Views
Last Modified: 2013-12-24
Writing a very simple coldfusion query to add vacancies to a mssql table.
<cfscript>
      /**
      * Returns a UUID in the Microsoft form.
      *
      * @return Returns a string.
      * @author Nathan Dintenfass (nathan@changemedia.com)
      * @version 1, July 17, 2001
      */
      function CreateGUID() {
            return insert("-", CreateUUID(), 23);
      }
      </cfscript>
<cfquery datasource="mydatasource_etcetc">
 INSERT INTO VACANCY(VACANCY_ID)
 VALUES(<CFQUERYPARAM VALUE="#CreateGUID()#" CFSQLTYPE="CF_SQL_VARCHAR" maxlength="50">)
</cfquery>
I'm using the script to creat a guid that mssql is happy with however whenever I try to run this i get the following error
[Macromedia][SQLServer JDBC Driver][SQLServer]Violation of UNIQUE KEY constraint 'IX_VACANCY_UNIQUE'. Cannot insert duplicate key in object 'dbo.VACANCY'.

there are no other entries in the table so this is an attempt to add the first scalled down vacancy record. There is only one PK field in the table - VACANCY_ID

i know this is probably something very obvious but I cannot see it & I need help!!


   
0
Comment
Question by:wibber1
7 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 35733017
What's the definition of the constraint 'IX_VACANCY_UNIQUE'? Can you post the whole table ddl?

0
 
LVL 52

Expert Comment

by:_agx_
ID: 35733169
Plus ...

>> There is only one PK field in the table - VACANCY_ID <<
>> Violation of UNIQUE KEY constraint 'IX_VACANCY_UNIQUE'. <<

     In addition, PK's are unique by definition. So normally you wouldn't need a
     unique constraint on top of that.  Unless that's just a misleading error message
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35735741
If field is define as unique then no one insert a duplicate value
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:psvineesh
ID: 35737741
FYI
Unique key filed should not be duplicate.
But it allows NULL values.

Thanks
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35740371
@wibber1 - we need to see the definition of the table, including any indexes and constraints.
0
 
LVL 6

Expert Comment

by:reiters
ID: 35839766
<cfquery name="xxxxx" datasource="mydata" username="uid" password="pass">
SET IDENTITY_INSERT [#strTableName#] ON;
</cfquery>

<cfquery name="xxxxx" datasource="mydata" username="uid" password="pass">
INSERT INTO VACANCY(VACANCY_ID)
 VALUES(<CFQUERYPARAM VALUE="#CreateGUID()#" CFSQLTYPE="CF_SQL_VARCHAR" maxlength="50">
</cfquery>

<cfquery name="xxxxx" datasource="mydata" username="uid" password="pass">
SET IDENTITY_INSERT [#strTableName#] OFF;
</cfquery>
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 35840941
>>  SET IDENTITY_INSERT [#strTableName#] ON;  <<

Enabling identity insert still won't let you insert duplicates which is what the unique key violation error was complaining about. Though I don't see any mention of identity columns in the original post.

Still waiting for the asker to clarify the original question ...
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question