asked on

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

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!!

_agx_

What's the definition of the constraint 'IX_VACANCY_UNIQUE'? Can you post the whole table ddl?

_agx_

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

Alpesh Patel

If field is define as unique then no one insert a duplicate value

psvineesh

FYI
Unique key filed should not be duplicate.
But it allows NULL values.

Thanks

_agx_

@wibber1 - we need to see the definition of the table, including any indexes and constraints.

reiters

<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>

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial