Unique Index Conflict On Insert Into
Jun 18, 2008
I get this message when doing an insert into :
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.ElementLocalCharacterised' with unique index 'ElementLocalCharacterised_uq'.
The statement has been terminated.
Now my question is : does this error mean that all inserts are done, despite those where the unique key produced a conflict? Or does it mean that none of the inserts are done (all are rejected because at least one gave a conflict) ?
If the second is the case I would like to know if it is possible to execute the insert into in a way that all inserts are done despite those that raise a conflict?
Thanks a lot for any suggestion !
Regards,
Fabianus
my favorit hoster is ASPnix : www.aspnix.com !
View 3 Replies
ADVERTISEMENT
May 27, 2002
Hi,
I am making a program in Visual Basic .NET with SQL Server 2000.
I have a table "MyTable" with a primary key named "Id". The primary key is 'Create Unique' checked and 'Index' selected. When I insert all the fields required, except "Id" of course, I need the new record's "Id" in my VisualBasic program, but I don't know how...
I must do one of them, but don't know how either of them:
-Create a trigger on insertion that will send to the user that sended the insert command the "Id" of the record just created.
or
-get the command in Visual Basic that will send the Insert command with a return field ("Id")
Thanks in advance,
Sebastien Anselmo
View 6 Replies
View Related
Jun 22, 2007
All,Just want to make sure that I understand what's going on here.I have a table with IGNORE_DUP_KEY set on a unique, multi-columnindex.What I'm seeing is this:1) When performing a BULK INSERT, the UNIQUE index is not beingrespected and rows which violate the unique index are inserted.2) When performing a regular INSERT, the UNIQUE index is beingrespected and rows which violate the unique index ARE NOT inserted.Is this expected behavior.Also, I have some questions, given the index described.Q1) Will a regular INSERT that attempts to insert duplicate data getan error back or just a warning?Q2) How can I set things up so that a BULK INSERT would NOT allowduplicates to be entered into the table?Thanks,Wes Gamble
View 2 Replies
View Related
Feb 28, 2007
I have 1 client who keeps running into the following error on the subscriber and merge agents >
€śCannot insert duplicate key row in object 'MSmerge_genhistory' with unique index 'unc1MSmerge_genhistory'.€?
Last time we got this error I ran a reindex on table MSmerge_genhistory on the publisher database, I then successfully generated a new snapshot and the subscribers started to synchronize again. This time around I keep getting the error even after I follow these steps (I also ran all the jobs to clean up replication). The last time I ran into this error I created a job to reindex msmerge_genhistory on a nightly bases in an effort to avoid this problem. Can somebody please provide me with a workaround and also the reason why this error occurs in the first place.
Thank you in advanced,
Pauly C
View 6 Replies
View Related
Aug 22, 2007
Hi.
I have been recently redesigning my tables - creating FK
relationships from child tables to the PK userid in the Users table.
The specifics of what I did and why can be seen here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1968856&SiteID=1
But, now I am getting the below error:
Cannot insert duplicate key row in object 'dbo.lastlogin' with unique index 'IX_lastlogin'.
The statement has been terminated.
Or, for that matter, SavedSearches or any other table where I need to
insert the same userid twice. I can see why I would want to avoid duplicates in the Users table. But, for lastlogin, savedsearches, and
a few of my other tables, the same user may account for multiple rows.
Any suggestions as to where I messed up and how to deal with this?
Thanks.
DBO.USERS
Code Snippet
CREATE TABLE [dbo].[users](
[userid] [int] IDENTITY(1,1) NOT NULL,
[lastname] [varchar](50) NULL,
[firstname] [varchar](50) NULL,
[email] [varchar](50) NOT NULL,
[alternateemail] [varchar](50) NULL,
[password] [varchar](50) NOT NULL,
[role] [varchar](10) NOT NULL,
[securityquestion] [varchar](50) NOT NULL,
[securityanswer] [varchar](50) NOT NULL,
[zipcode] [int] NOT NULL,
[birthmonth] [tinyint] NOT NULL,
[birthday] [tinyint] NOT NULL,
[birthyear] [int] NOT NULL,
[gender] [varchar](10) NULL,
[city] [varchar](50) NULL,
[state] [varchar](50) NULL,
[country] [varchar](50) NULL,
[registerdate] [datetime] NOT NULL,
[editdate] [datetime] NULL,
[confirmed] [bit] NULL CONSTRAINT [DF__Users__confirmed__4CC05EF3] DEFAULT ((0)),
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_email] UNIQUE NONCLUSTERED
(
[email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/****** Object: Table [dbo].[lastlogin] Script Date: 08/22/2007 14:16:16 ******/
SET ANSI_NULLS ON
DBO.SAVEDSEARCHES
CREATE TABLE [dbo].[savedsearches](
[savedsearchesid] [int] IDENTITY(1,1) NOT NULL,
[searchname] [varchar](50) NOT NULL,
[userid] [int] NOT NULL,
[date] [datetime] NULL,
[isdefault] [bit] NULL,
[gender] [char](10) NULL,
[startyear] [varchar](50) NULL,
[endyear] [varchar](50) NULL,
[country] [varchar](50) NULL,
[miles] [int] NULL,
[pictures] [varchar](50) NULL,
[postal] [int] NULL,
[sort] [tinyint] NULL,
[photostring] [varchar](50) NULL,
[orderby] [tinyint] NULL,
CONSTRAINT [PK_SavedSearches] PRIMARY KEY CLUSTERED
(
[userid] ASC,
[searchname] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[savedsearches] WITH NOCHECK ADD CONSTRAINT [FK_savedsearches_users] FOREIGN KEY([userid])
REFERENCES [dbo].[users] ([userid])
GO
ALTER TABLE [dbo].[savedsearches] CHECK CONSTRAINT [FK_savedsearches_users]
GO
SET QUOTED_IDENTIFIER ON
GO
The following insert statement returned the error in the subject because userid = 32 already exists in the Users table.
INSERT INTO lastlogin
VALUES (32, CONVERT(VARCHAR(26), GETDATE(), 109), 1, CONVERT(VARCHAR(26), GETDATE(), 109))
DBO.LASTLOGIN
Code Snippet
CREATE TABLE [dbo].[lastlogin](
[lastloginid] [int] IDENTITY(1,1) NOT NULL,
[userid] [int] NOT NULL,
[date] [datetime] NOT NULL,
[status] [bit] NOT NULL CONSTRAINT [DF_lastlogin_status] DEFAULT ((0)),
[activity] [datetime] NOT NULL CONSTRAINT [DF_lastlogin_activity] DEFAULT (getutcdate()),
[online] AS (case when [status]=(1) AND datediff(minute,[activity],getutcdate())<(30) then (1) else (0) end),
CONSTRAINT [PK_lastlogin] PRIMARY KEY CLUSTERED
(
[date] ASC,
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[lastlogin] WITH NOCHECK ADD CONSTRAINT [FK_lastlogin_users] FOREIGN KEY([userid])
REFERENCES [dbo].[users] ([userid])
GO
ALTER TABLE [dbo].[lastlogin] CHECK CONSTRAINT [FK_lastlogin_users]
View 1 Replies
View Related
Aug 4, 2014
I have an issue where I am getting an error on an unique index.
I know why I am getting the error but not sure how to get around it.
The query does a check on whether a unique value exists in the Insert/Select. If I run it one record at a time (SELECT TOP 1...) it works fine and just won't update it if the record exists.
But if I do it in a batch, I get the error. I assume this is because it does the checking on the file before records are written out and then writes out the records one at a time from a temporary table.
It thinks all the records are unique because it compares the records one at a time to the original table (where there would be no duplicates). But it doesn't check the records against each other. Then when it actually writes out the record, the duplicate is there.
How do I do a batch where the Insert/Select would write out the records without the duplicates as it does when I do it one record at a time.
CREATE TABLE #TestTable
(
Name varchar(50),
Email varchar (40)
)
Insert #TestTable (Name,Email) Values('Tom', 'tom@aol.com')
[Code] .....
View 1 Replies
View Related
Jan 16, 2007
We are developing a project that is expected to hold TB of data and the back end used is SQL Server 2005.
I have the following problem
I have applied Nonclustered index over a column on a table.
Designed a SP for insertion which caters for updation incase the criteria based on the input is met.
The logic goes like this
Incase there exists a row containing the value of the column that is indexed for uniqueness, there should be updation. If not there should be a new row created.
However often there is an error message that is placed above. This happens only on some of the SPs and only on rare occasions.
Can any body tell me if there is any problem with the SQL Server 2005
Thanks in advance
R Suresh, SQLDBA
View 8 Replies
View Related
Jul 5, 2015
This index is not unique
ix_report_history_creative_id
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
The statement has been terminated.
View 6 Replies
View Related
Jul 20, 2005
I created a view that joins two tables (tblOrder and tblProduct). The fieldProductID is common to both. When I save the View I get the following errormessage: "Column names in each view must be unique." Is there a way aroundthat? Should I be naming my Key Fields and Foreign Key fields differently?I'm new to MS-SQL. Thanks for your help.Louis
View 2 Replies
View Related
Sep 22, 2004
A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.
View 8 Replies
View Related
Jul 20, 2005
HelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil
View 5 Replies
View Related
Jun 24, 2006
What's the difference in the effect of the followings:
CREATE UNIQUE NONCLUSTERED INDEX
and
ALTER TABLE dbo.titles ADD CONSTRAINT
titleind UNIQUE NONCLUSTERED
I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?
View 1 Replies
View Related
Jul 20, 2005
i have a dts package that is going to run at night and pull data from2 tables in an Oracle db and load it to 2 staging tables that mypackage creates in sql server 2000. i then execute a few sprocs toand columns, indexes, etc. after that all happens successfully i wantto delete the two production tables and rename the staging tables totake their place.the problem is that when i run the sproc to add the indexes,constraints, etc i get an error because objects with the same nameexist on the 2 production tables. i can't delete the productiontables until the very last step. i thought about generating a randomnumber in my sproc and using it as the name of the index but thatseems crude and messy. any ideas?
View 2 Replies
View Related
Mar 7, 2001
Hi everyone,
I need urgent help to resolve this issue...
As far as the performance goes which one is better..
Unique Index(col1, col2) OR Unique constraint(col1, col2) ?
Unique constraint automatically adds a unique index
and unique index takes care of uniqueness then whats the use of unique constraint ?
Which one do one use ?
thanks
sonali
View 4 Replies
View Related
Jan 20, 2006
BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?
View 2 Replies
View Related
Mar 26, 2008
hi team,
.Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?
View 12 Replies
View Related
Nov 13, 2007
What 's difference between Unique key and unique index in SQL server 2005?
View 9 Replies
View Related
Apr 23, 2008
Dear all,
i've a small doubt....
i've a table like this....
create table t(sno nchar(2))
go
insert into t select 'Alt+9'
(here i'm taking the value which is the output of press alt key with 9 and release).
how can iget the exact result for this?
i'm getting the question mark there when i'm selecting the data.
for the rest of the characters, i'm getting the exact and expected result
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 2 Replies
View Related
Nov 21, 2006
there have a exception when I trying to run a insert query
the exception is occur on cmd.ExecuteReder(); and shows
Insert Query conflict with Foreign key..
why? and how to resolve it?
thank you
View 1 Replies
View Related
Mar 29, 2004
Just found out that creating a unique index does not create a unique constraint, but creating a unique constraint creates unique index.
But effectively they do the same thing.
View 3 Replies
View Related
May 8, 2008
How to findout whether a Index is unique or not?
------------------------
I think, therefore I am - Rene Descartes
View 2 Replies
View Related
Mar 26, 2007
if this question is inappropriate here, I apologize (it's at least obliquely related). I have been using ssno as a unique key in a datawarehouse I have been working on because all of the component systems have had it. I now have a database to add where ssno is not available. I have first, last address, city, state,zip and dob.
Question is, how to construct a unique identifier from those components. If not unique, then at least usable?
Again, if this post is wrong here, I apologize
Thanks for any input
Walter
View 1 Replies
View Related
Feb 6, 2007
This is for SQL 2000 (SP 2) using Enterprise Manager. I have a table with a unique index comprised of several int fields. The index needs to include an additional bit field that is part of the table. But when I go to modify the index, the bit field name doesn't appear in the Column Name list.
Can anyone shed any light on the problem?
Thanks.
View 5 Replies
View Related
May 21, 2002
I have read that you get better performance with unique indexes rather than non-unique indexes.
I have experimented with this in SQL 2000. I have two identical tables (with about 250000 rows each) with a 12-character unique column. In one table I define it as a regular index and in the other I define it as a unique index. No matter what I try I get identical performance, and the query optimizer shows an identical plan.
I even tried clauses such as
WHERE 1 < (SELECT COUNT(*) FROM TheTable
WHERE key_column = OtherTable.key_column)
which should obviously return nothing if TheTable.key_column is unique. However the query still ran a long time no matter if the index is unique or not.
I have also tried a unique constraint instead of a unique index and got the same (non)results.
Can anyone come up with an example where creating a unique index actually makes a performance difference?
View 1 Replies
View Related
Dec 17, 2006
Hello !
for MS SQL 2000
how can i set an unique index on Serial column but only if Serial IS NOT NULL
CREATE UNIQUE INDEX [IX_Product] ON [Product]([Serial]) ON [PRIMARY]
i can have 100 rows with a NULL Serial
thank you
View 9 Replies
View Related
Apr 19, 2013
I find to be able to have multiple NULL entries in the following constraint:
Code:
CREATE UNIQUE INDEX my_uidx ON my_table(my_col1,my_col2) WHERE ??? IS NOT NULL;
But is not possible to check multiple columns in "WHERE".
I using SQL Server 2012 Express
View 3 Replies
View Related
Feb 12, 2004
I'm trying to weight the pros and cons of unique constraints and unique indexes. I understand that creating a unique constraint also creates an index. If that is the case, why not just use a unique index? Could someone give me an example of when you would want an unique constraint over an unique indexes
Thanks in advance
View 7 Replies
View Related
May 7, 2008
What is the difference between unqiue constraint and unique index? What are the pros and cons? Are they interchangable?
------------------------
I think, therefore I am - Rene Descartes
View 4 Replies
View Related
Mar 4, 2008
What is unique index in sql server 2k?
plz some one help me out, thanx
Rahul Arora
07 Batch
NCCE Israna,
######################
IMPOSSIBLE = I+M+POSSIBLE
View 2 Replies
View Related
Jul 20, 2005
HiI tried the following from the help file...When you create or modify a unique index, you can set an option toignore duplicate keys. If this option is set and you attempt to createduplicate keys by adding or updating data that affects multiple rows(with the INSERT or UPDATE statement), the row that causes theduplicates is not added or, in the case of an update, discarded.For example, if you try to update "Smith" to "Jones" in a table where"Jones" already exists, you end up with one "Jones" and no "Smith" inthe resulting table. The original "Smith" row is lost because anUPDATE statement is actually a DELETE followed by an INSERT. "Smith"was deleted and the attempt to insert an additional "Jones" failed.The whole transaction cannot be rolled back because the purpose ofthis option is to allow a transaction in spite of the presence ofduplicates.But when I did it the original "Smith" row was not lost.I am doing something wrong or is the help file incorrect.Dan
View 3 Replies
View Related
Jul 20, 2005
When I add a unique key constraint to column in SQL 6.5 why does it alsocreate an index. e.g. In the table subaccounts I added a unique keyconstraint for the column login and SQL creates an index with the nameUQ_SubAccounts_2__19 (UKC).Does this also mean that there is no need to create an index for thiscolumn?thxMansoor
View 2 Replies
View Related
Aug 21, 2007
Hello.
I have a question. I need to make a double unique index on a table. for example: I have 2 columns, ColumnA and ColumnB. ColumnA can have duplicate values, so is ColumnB, but it should be impossible to have duplicate values on both columns. for example:
Row 1:
ColumnA = 1, ColumnB = 2
Row2:
ColumnA = 1, ColumnB = 2
this shouldn't be possible.
Row1:
ColumnA = 1, ColumnB = 2
Row2:
ColumnA = 1, ColumnB = 3
this should be possible
is there any way I can do this?
thanks in advance
View 1 Replies
View Related
Aug 20, 2007
When not using any enforced relationships in a database (which I know we should, but I like most of you, have inherited sub-optimal design), is there any real difference between using a Primary Key (to which no foreign keys are tied) and using a Unique Clustered index?
Thanks for your thoughts
View 3 Replies
View Related
