Cannot Insert Duplicate Key Row In Object 'MSmerge_genhistory' With Unique Index 'unc1MSmerge_genhistory'
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 Complete Forum Thread with Replies
Related Forum Messages:
Cannot Insert Duplicate Key Row In Object 'dbo.lastlogin' With Unique Index 'IX_lastlogin'.
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 Replies !
Cannot Insert Duplicate Key Row In Object 'dbo.blabla..' With Unique Index 'Idx_blablabl'. The Statement Has Been Terminated. At
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 Replies !
Unique Index Returns Duplicate Rows
We are running the following query, which has a unique index on Table_2 (col1 and sys1), and Column col1 from Table_1 is unique. select top 100 s.*, x.col1 from Table_1 s left outer join Table_2 x on x.col1 = s.col1 and x.sys1 = 'SYSTEM0' Unfortunately this query returns duplicate rows. And every time the result is different But once we dbcc dbreindex the unique index on Table_2, the result will not have any dups. Any ideas? Thanks Steve
View Replies !
Update Rows To Resolve Issues About Duplicate Keys On Create Unique Index
Hi there ...here comes a tricky one. I have a database table which needs to make the Index "ParentREF, UniqueName" unique - but this fails because duplicate keys are found. Thus I now need to cleanup these duplicate rows - but I cannot just delete the duplicates, because they might have rows in detail tables. This means that all duplicate rows needs an update on the "UniqueName" value - but not the first (valid) one! I can find those rows by SELECT OID, UniqueName, ParentREF, CreatedUTC, ModifiedUTC FROM dbo.CmsContent AS table0 WHERE EXISTS ( SELECT OID, UniqueName, ParentREF FROM dbo.CmsContent AS table1 WHERE table0.ParentREF = table1.ParentREF AND table0.UniqueName = table1.UniqueName AND table0.OID != table1.OID ) ORDER BY ParentREF, UniqueName, ModifiedUTC desc ...but I struggle to make the required SQL (SP?) to update the "invalid" rows. Note: the "valid" row is the one with the newest ModifiedUTC value - this row must kept unchanged! ATM the preferred (cause easiest) way is to rename the invalid rows with UniqueName = OID because if I use any other name I risk to create another double entry. Thanks in advance to whoever can help me
View Replies !
SP Causes The Error Violation Of UNIQUE KEY Constraint Cannot Insert Duplicate Key
The following SP causes the error "Violation of UNIQUE KEY constraint 'AlumniID'. Cannot insert duplicate key in object [table name]. The statement has been terminated." AlumniID is the table's PK and is set to autoincrement. I'd appreciate any help or suggestions. 1 ALTER PROCEDURE dbo.sp_CreateUser 2 3 @UserID uniqueidentifier, 4 @UserName nvarchar(128), 5 @Email nvarchar(50), 6 @FirstName nvarchar(25), 7 @LastName nvarchar(50), 8 @Teacher nvarchar(25), 9 @GradYr int 10 11 AS 12 SET NOCOUNT ON; 13 --DECLARE @UserID uniqueidentifier 14 --SELECT @UserID = NULL 15 --SELECT @UserID = UserID FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName-- AND @ApplicationId = ApplicationId 16 INSERT INTO [table] 17 (UserID,UserName,Email,FirstName,LastName,Teacher,GradYr) 18 VALUES (@UserID,@UserName,@Email,@FirstName,@LastName,@Teacher,@GradYr
View Replies !
Cannot Insert Duplicate Key Row In Object
The following quote is the exact error message that I get when I attempt to create a pull subscription. The STAppointment is one of a 15 databases that need to replicate inorder for the application SalonTouch to run properly. What is causing this error? Is it user rights? Is it a problem with sql2005? Am I missing a step? Is there a problem with the database I am working with? What else? TITLE: New Subscription Wizard ------------------------------ SQL Server could not create a subscription for Subscriber 'D6LHWQ91PORTSUN9'. ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Cannot insert duplicate key row in object 'dbo.MSmerge_replinfo' with unique index 'uc1MSmerge_replinfo'. The subscription could not be created. The subscription properties table 'MSsubscription_properties' does not exist in the current database. Changed database context to 'STAppointment'. The statement has been terminated. (Microsoft SQL Server, Error: 2601) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=2601&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------
View Replies !
Cannot Insert Duplicate Key In Object 'dbo.RunningJobs'
I'm using SSRS 2005 on a Server 2003 machine. Some of my reports run fine but others take a long time to run and sometimes fail with an http error 503. After a report errror happens I find entries in the event log that says: cannot open a connection to the report server When I look at the log files for Reporting services I see an error caused by a primary key violation on the Running Jobs Table: Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs' Does anyone know why do I get such errors? Bellow is the error message in the logs: w3wp!library!6!21/08/2006-09:30:22:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs'. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.RunningJobsDb.AddRunningJobs(Hashtable runningJobs)
View Replies !
Cannot Insert Duplicate Key In Object 'dbo.RunningJobs'
We continue to recieve he following error on our RS 2005 SP1 Reporting Services installation: Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs' This causes our server to become unresponsive and some reports to not render, Its becoming quite common. We reset IIS to correct it but is causes a service interuption. I know that it had been talked about before as something that may be fixed in SP2. But I do not see this referenced. Does anybody know of any fix for this? Thanks Ron
View Replies !
Unique Index Conflict On Insert Into
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 Replies !
How To Return Primary Unique Index Key On Insert
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 Replies !
BULK INSERT Ignores UNIQUE Index With IGNORE_DUP_KEY Set?
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 Replies !
CREATE TABLE DUPLICATE OBJECT/DUPLICATE FIELD NAME ERROR Msg 2714
Hello Everyone: I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer. When I try to create the tables, for the query, I am getting the following error: Msg 2714, Level 16, State 4, Line 12 There is already an object named 'UserID' in the database. Msg 1750, Level 16, State 0, Line 12 Could not create constraint. See previous errors. I have duplicated this error with the following script: USE [testing] IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL DROP TABLE [testing].[dbo].[users1] CREATE TABLE [testing].[dbo].[users1] ( [UserID] bigint NOT NULL, [Name] nvarchar(25) NULL, CONSTRAINT [UserID] PRIMARY KEY (UserID) ) IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL DROP TABLE [testing].[dbo].[users2] CREATE TABLE [testing].[dbo].[users2] ( [UserID] bigint NOT NULL, [Name] nvarchar(25) NULL, CONSTRAINT [UserID] PRIMARY KEY (UserID) ) IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL DROP TABLE [testing].[dbo].[users3] CREATE TABLE [testing].[dbo].[users3] ( [UserID] bigint NOT NULL, [Name] nvarchar(25) NULL, CONSTRAINT [UserID] PRIMARY KEY (UserID) ) I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database. I think that the schema is only allowing a single UserID primary key. How do I fix this? TIA
View Replies !
Unique Constraint And Unique Index, What's The Difference?
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 Replies !
Unique Constraint Vs Unique Index
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 Replies !
Unique Index Vs Unique Constraint
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 Replies !
Duplicate Key But Unique Rows.
I have a large table that consists of the columns zip, state, city, county. The primary key "zip" has duplicates but the rows are unique. How do I filter out only the duplicate zips. Randy Garland
View Replies !
Elimenating Duplicate Keys With Unique Row.
I have a large table that consists of the columns zip, state, city, county. The primary key "zip" has duplicates but the rows are unique. How do I filter out only the duplicate zips. So in effect I only have one row per unique key. Randy Garland if you just want a list of all rows with duplicate zipcodes then ... SELECT * FROM TableName WHERE zip IN ( SELECT zip FROM TableName GROUP BY zip HAVING COUNT(*)>1 ) Duncan Duncan, I tried this but it does not return one row per key. Randy Garland
View Replies !
Renaming Duplicate Row Data To Be Unique?
I just converted an old non-relational database into something that MS SQL likes. The old primary keys were broken up into two columns, one being useful. The column I need to use has some rows with the same values in them. I am looking for some way in a SQL script to look for the duplicate rows and add "_X" to the data where X is a value incremented by 1 for each duplicate row found. For example, 3 duplicate rows with "5443aa" would return "5443aa", "5443aa_1","5443aa_2". Any ideas? --MartinZ
View Replies !
Unique Constraint Does Not Permit Duplicate NULL Values
After adding a Unique constraint to a database I cannot add more than one record with a null value for the constrained field. I've tried both adding the constraint to an empty table as well as a table with multiple null values already in the subject field; both efforts have failed. According to BOL SQL-7 allows Unique Constraints on fields with Null values. Am I missing a step? I do need to allow nulls in the field yet ensure that when there is a non-null value it is unique. The SQL statement I've used is: ALTER TABLE tbl_MasterUIC ADD CONSTRAINT uniquesamplenbr UNIQUE NONCLUSTERED (samplenbr) Thanks for any and all suggestions
View Replies !
Unique Index
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 Replies !
Unique Index
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 Replies !
Unique Index
How to findout whether a Index is unique or not? ------------------------ I think, therefore I am - Rene Descartes
View Replies !
Can't Add Bit Column To Unique Index
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 Replies !
Creating A Unique Index
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 Replies !
Unique Constraint Index
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 Replies !
UNIQUE INDEX If Not NULL
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 Replies !
Unique Constraint/index
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 Replies !
Better Performance With Unique Index?
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 Replies !
Double Unique Index
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 Replies !
Unique Index, SQL 2005
Is there a simple way to find properties of an index for a table? Specifically I am looking for the unique property. I currently have code to to do this, however I feel like there must be some easier way. I am using .net 2005 and SQL 2005 coding in c#. Thanks for any help! Code Snippet _conn.Open(); SqlCommand cmd = new SqlCommand("sp_helpindex", _conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@objname", "TABLENAME")); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(results); foreach (DataRow row in results.Tables[0].Rows) { ArrayList names = new ArrayList(); DataTable dtUnique = new DataTable(); SqlCommand cmd2 = this._conn.CreateCommand(); cmd2.CommandText = String.Format("SELECT INDEXPROPERTY(OBJECT_ID('TABLENAME'), 'INDEXNAME', 'IsUnique') AS isUnique"); dtUnique.Load(cmd2.ExecuteReader()); bool unique = Convert.ToBoolean(dtUnique.Rows[0]["isUnique"]); if (unique) names.Add(tableName); } _conn.Close();
View Replies !
PK Vs Unique Clustered Index
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 Replies !
Unique Constraint/Index
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 Replies !
Msg 2714, Duplicate Object Error
Existing database (upgrade to SS2k5) has four tables with same primary key columns (UserID). Using CREATE TABLE with a specified [databasename].[dbo].[table1] schema is giving the following error message: Msg 2714, Level 16, State 4, Line 12 There is already an object named 'UserID' in the database. Msg 1750, Level 16, State 0, Line 12 Could not create constraint. See previous errors. I have duplicated the error with the following script: USE [testing] IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL DROP TABLE [testing].[dbo].[users1] CREATE TABLE [testing].[dbo].[users1] ( [UserID] bigint NOT NULL, [Name] nvarchar(25) NULL, CONSTRAINT [UserID] PRIMARY KEY (UserID) ) IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL DROP TABLE [testing].[dbo].[users2] CREATE TABLE [testing].[dbo].[users2] ( [UserID] bigint NOT NULL, [Name] nvarchar(25) NULL, CONSTRAINT [UserID] PRIMARY KEY (UserID) ) IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL DROP TABLE [testing].[dbo].[users3] CREATE TABLE [testing].[dbo].[users3] ( [UserID] bigint NOT NULL, [Name] nvarchar(25) NULL, CONSTRAINT [UserID] PRIMARY KEY (UserID) ) I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors within a database. I think that the schema is only allowing a single UserID primary key. How do I fix this? TIA
View Replies !
How To Create Unique Secondary Index ?? HELP
I have created the companyid as Primary Key.How to create a unique secondary index on Company Name. To avoid inserting duplicate records in database with the same companyname. I m creatin database in sql server 2005 with asp.net C# 2005. I know one way is write the query if not exists at the time of insert.But,i want to know is there anyother way to make a unique secondary index for the companyname on the company tablethanxs
View Replies !
Unique Index For Two Columns In A Table
Hi,I would like to add a unique index that consists of two fields in atable.e.g. tbl_A (field1,field2) -- field1 & field2 Indexed and combinationmust be Unique.Can anyone tell me the actual sql syntax to create this index?Thanks,June.
View Replies !
Create Unique Index Error
Does anybody know the significance of 3 in the following error message? "CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 3. " Thank you
View Replies !
Unique Index On X Columns But Not Primary Key
my table : CREATE TABLE [dbo].[users] ( [ID] [int] NOT NULL , [A1] [nvarchar] (100) NULL , [A2] [nvarchar] (100) NULL , [A3] [nvarchar] (100) NULL ) ON [PRIMARY] i must keep ID columns as primary key ALTER TABLE [dbo].[users] WITH NOCHECK ADD CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] but now A1+A2 must be unique how can i do it ? thank you
View Replies !
Replication- Converts PK To Unique Index
Hi, When I replicate from a 7.0 publication to a 2000 subscription though a 2000 distribution, all the primary keys gets converted to unique clustered index in the subscription. Whereas , when I use 7.0 distribution keys stay keys in the subscription. Can you suggest how can I fix the problem? Is it a bug? Thanks in advance. Hassan
View Replies !
Unique Index With Null Values
Is there any equivalent to MS Access unique index that allows null values ( ignore null values) in SQL Server? how can i create a unique index that ignores null values? Thanks in advance.
View Replies !
|
TRACKER
