Unique Index Vs Unique Constraints
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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
Failed To Enable Constraints. One Or More Rows Contain Values Violating Non-null, Unique, Or Foreign-key Constraints.
Hi, I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error. e.g MOB 401.908.804 - Fails 0401.907.324 - okay 8239 9082 (pager) - fails Anyone got an idea on how to fix this???? Regards.. Peter.
View Replies !
UGH! Failed To Enable Constraints. One Or More Rows Contain Values Violating Non-null, Unique, Or Foreign-key Constraints.
I know this is probably a flick of a switch but I cannot figure out which switch. Setup is SQL Server / Stored Procedures / DAL / BLL(skipped for testing) / PL. The stored procedure queries from only one table and two columns are ignored because they are being phased out. I can run the stored procedure and preview the data in the DAL but when I create a page with an ODS linked to the DAL and a GridView I get this error. I checked every column that does not allow nulls and they all have values. I checked unique columns (ID is the only unique and is Identity=Yes in the table definition). I checked foreign-key columns for values that are not in the foreign table and there are none. Any ideas why do I get this? Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
View Replies !
Unique Constraints
I need to ensure that each record in a table is unique in terms of two fields. E.g. Job No. 1 and Phase 1 cannot exist twice. I've tried to achieve this using a compound primary key but am having no joy. Im sure its a simple issue, any suggestions? Thanks Alex
View Replies !
Check / Unique Constraints
Hello, I want to write a unique constraint that applies to more than one column. What I mean is that the uniqueness should be that if column A is 5 and column B is 3 no other row where A and B has those values can exist. Do I write this as a check constraint ? Or how do I do it ? Also, is there anyone who knows some good reading on how to use Link Tables (many to many relations) in MS SQL Server ?
View Replies !
What Is The Constraints Expression For Unique(Column)?
I am in the "Check Constraints" window trying to add a constraint that during inserts only allow distinct Column2 insertions. What is the Expression for that? I tried UNIQUE(Column2) it give me an error. Here is my table definition: Column Name Data Type Allow Nulls -------------------------------------------------------------------------------------------- Column1(PrimaryKey) int No Column2 nvarchar(MAX) No
View Replies !
Checking For Unique Constraints During A Batch Update
hello . i have a grid for a table that gets updated with recordset.updatebatch for a multi-user application. the problem is that some of the table fields have to be unique now imagine the next situation. user A opens the form user B opens the form user A writes '1000' in the unique field user A saves the recordset with updatebatch user B writes '1000' in the unique field user B saves the recordset with updatebatch now there will be two records with the field '1000' ! how can i avoid this ? i cannot check for unique during the update event of the grid because it should check during the time it is saving and not when it is just entering data without having updated the recordset thanks !
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 !
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 !
Error While Droping Unique Index
Hi, I get the following error message while droping unique index. Server: Msg 3723, Level 16, State 5, Line 1 An explicit DROP INDEX is not allowed on index 'dbo.ALEG.IX_ALEG'. It is being used for UNIQUE KEY constraint enforcement. Could you please how the index can be droped. Thanks
View Replies !
How Do You Determine An Index Is Unique From Sysindexes?
I need to write a script which will respond differently if a table's index forces uniqueness. I am using SQL 7.0 and I am using the [sysindexes] table of my database to examine those indexes. I am using the [id] field from the [sysobjects] table for the table I am examining and I am using that [id] field to find like-valued [id] fields in the [sysindexes] table. My problem is that I can't seem to determine, from an examination of the [sysindexes] table, whether, or not, an index is unique. When adding an index, and checking the unique property of that index, the values that I get in the [status] field of [sysindexes] are different from the values which are supposed to show, at least according to the sysindexes (T-SQL) entry in SQL Server Books Online. For instance, I have a table which combines two text fields which comprise the only key on a table. The unique and clustered boxes are checked in the index setup screen. When I look in the [sysindexes] table, the value in the [status] field is 2113554, which is not a value I see in the books online page. According to my books online page, a unique index should have a value of 2 in the [status] field and a clustered index should have a value of 16 in the [status] field. My assumption is that I should see a value of 18 in that [status] field, not 2113554. I looks like the books online entry might be out-of-date because the field that is labeled [reserved1] in my books online page, is labeled [StatVersion] in my actual [sysindexes] table. That [StatVersion] field looks suspiciously like a Status Version field, possibly indicating that the Status field has undergone some sort of version revision? Is anyone familiar with this stuff? Thanks. Ken
View Replies !
Question On Primary Key, Unique Index
Question: I have a test table like this CREATE TABLE [dbo].[Test]( [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [addr] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ( [name] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] But when I tried to input my data like 'abc', '123 abc' 'abc ','123 abc' SQL server won't recognize 'abc' and 'abc ' is a different value if the last character is a space. Is there a way to make it as a different value? I tried to drop the primary and input the data. When I ran a group by the name column, 'abc' show 2 instead of 1. Seems SQL server is trying to ignore the space at the end too. I also noticed unique index have the same problem too. Please help.
View Replies !
Partition Switch And Non Unique Index
I have a table partitioned by month. There are no primary key. There is another table with the same structure that I use to load data for the current month. I can perform partition switch with no problem. I created non unique index on integer field in both tables. After this change partition switch does not work anymore. Here is an error that I am getting: 'ALTER TABLE SWITCH' statement failed. The table 'dbo.test' is partitioned while index 'IX_test' is not partitioned." Any help?
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 !
Changing Field For Unique Key Index
SQL 2000 Enterprise Manager won’t let me drop a unique key index and move it to another field. I get an error regarding ‘drop index not allowed because of unique key constraint enforcement. Is there anyway to change fields for unique keys in SQL Enterprise Manager? Thanks, Al
View Replies !
Create Unique Index On Multiple Columns
Hello, I will explain myself further. I want to make my table in such a way that no two colums have the same value for example: Row 1 - Column 1 = "cool" Row 1 - Column 3 = 91 Row 3 - Column 1 = "cool" Row 3 - Column 3 = 91 I dont care about one column having duplicate values, I want to protect against Column 1 and 3 having the same values on other rows. Is this possible to do in sql server?
View Replies !
What's The Difference? Unque Constraint And Unique Index, Etc.?
All,What's the difference between a unique contraint and unique?sementically, if you want a column contain unique values, it is acontraint. And an index is for searching/sort. The questions are:1. Does a unique constraint interally use unique index?2. If Yes to #1, I DO NOT need to create an index for search/sortpurpose, right?3. If Yes to #2, What's better?4. Also for Primary Key column, it is actually a special uniquecontraint. Not need to create index on PK column for searching/sorting,correct?5. Also for FK contraint, no need to create an index forsearching/sorting?ThanksJohn
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 !
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 !
Distinct Vs. Unique Index In Stored Procedure
Hi All, I'm struggling with this one. We have a Street database that can contain multiple entries for one record in our main table. We don't wish to pass duplicate rows back to our end user so I wish to get distinct recno's (the unique identifier for the main table) back from our Street Table. We have a stored procedure that currently uses Select distinct Recno but I think this is slower than if I used a unique index with ignore_dup_key. Problem is that when I alter the stored procedure it looks like the functionality of the LIKE function is somehow altered. For example Like 'King%' fails to return anything but Like 'King St%' does. I suspect it is the fact that the error "Server: Msg 3604, Level 16, State 1, Procedure" is being generated. How do I code around this server side.
View Replies !
Distributed Database Unique Index Creation
Can anyone suggest an appropriate strategy to create unique index's when a distributed database is used. As a bit of a background I have several SQL Server 2005 servers scattered around the country and I am trying to come up with a way to create a unique index i.e. one that hasn't been used in a particular table say Staff in any of the databases around the country. Performance is an issue particularly as our network isn't that fantastic has anyone done something similar and achieved reasonable speeds? Thanks ...
View Replies !
Unique INDEX With Upper/lower Case
Hi I can't create unique index like that: 1 - create table abc ( colZ varchar(10) ) 2 - insert into abc values ( "test") 3 - insert into abc values ("Test") 4 - create unique index idx_abc on abc ( colZ ) -- This doesnt work .... duplicate key was found for object name abc...... Is not there difference between "Test" and "test"? Can I work around this? cheers,
View Replies !
Gracefully Handle Unique Index Constraint
I am creating a new SSIS package where there is a flat file (CSV) I am importing. Well, there will be an end-user using a web UI to initiate the import of the file. However, this flat file is generated from another company and then uploaded to our network. This flat file has the potential to have duplicate rows that would have already been imported at a previous date. With the constraints in the table, we have guaranteed that there will not be duplicates added, but the SSIS package fails immediately upon attempting to insert a duplicate row - and the bulk update is rolled back. What I need to be able to do is load all of the rows that are not duplicates into the table. I am guessing that with my current approach, this is not possible. I am using a Data Flow task that converts the data in the flat file and then performs a bulk copy to load the data into the table. How can I either ignore duplicate rows, or otherwise gracefully handle this data import? - - - - - Will - - - - - http://www.strohlsitedesign.com http://blog.strohlsitedesign.com/ http://skins.strohlsitedesign.com/
View Replies !
|
TRACKER
