Unique Constraint Vs Unique Index In MS SQL 2000
Hello
What should I use for better perfomance since
unique constraint always use index ?
Thanks
Kamil
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 !
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 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 !
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 !
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 !
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 !
SQL Server 2005; Unique Constraint, Covering Index Question
Hi, When I create a unique constraint, SQL Server automatically creates an index on this constraint. So when I run the following... ALTER TABLE PersonsProjects WITH NOCHECK ADD CONSTRAINT NoDupes UNIQUE NONCLUSTERED (PersonID, ProjectID) ...SQL Server will create a composite index on PersonsProjects called NoDupes on PersonIDand ProjectID. Thing is, I need this index to include a third column Status since most queries use this column in conjunction with PersonID and ProjectID. If there was no index on this table, I would have created it as follows: CREATE UNIQUE INDEX NoDupes ON PersonsProjects (PersonID, ProjectID) INCLUDE (Status) WITH IGNORE_DUP_KEY But this won't enforce the unique constraint on PersonID and ProjectID when performing inserts and updates. Is there any way of creating a unique constraint with an included column? I would rather not have two indexes... NoDupes: PersonID,ProjectID New Index: PersonID,ProjectID INCLUDE Status ...so I'm trying to determine what other options that might be available...please advise. Thanks much.
View Replies !
A Unique Unique Constraint
Here is the table I created: create table Test ( [recId] [int] identity(1, 1) not null, [code] [varchar](50) not null, [prime] [bit] not null constraint [DF_Test_prime] default (cast(0 as bit)), constraint [PK_Test] primary key clustered ( [recId] ) with fillfactor = 90 on [primary] ) on [primary] go insert into Test (code, prime) values ('AVA', cast(1 as bit)) insert into Test (code, prime) values ('BUS', cast(1 as bit)) insert into Test (code, prime) values ('BUS', cast(0 as bit)) insert into Test (code, prime) values ('BUS', cast(0 as bit)) insert into Test (code, prime) values ('CAR', cast(1 as bit)) insert into Test (code, prime) values ('CAR', cast(0 as bit)) insert into Test (code, prime) values ('RLW', cast(1 as bit)) insert into Test (code, prime) values ('RLW', cast(0 as bit)) insert into Test (code, prime) values ('RLW', cast(0 as bit)) select * from Test I need to create a constraint on this table that will not allow me to have two rows that are prime for the same code. So the following insert statement should fail: -- This should fail insert into Test (code, prime) values ('RLW', cast(1 as bit)) Thanks for you help! Regards, Anand
View Replies !
Unique Constraint
What is the simplest way to add a unique constraint on a field of type varchar(7) that can allow any number of <NULL>'s? I only want to ensure that when this field is updated, it is updated with a value that has not been used. IF EXISTS (SELECT Project FROM tbProjects WHERE Project = @cProject) RAISERROR('Project number already used!',16,1) ELSE UPDATE tbProjects SET Project = @cProject WHERE ProjectID = @iProjectID GO Also, I cannot allow the user to chante the project field value once it is set. Any suggestions? Mike B
View Replies !
PK -vs- Unique Constraint
When creating a PRIMARY KEY Unique constraint on a table an Index is created automatically. However, when creating a UNIQUE Index a constraint is not automatically created. I'm wondering why I would choose to use a constraint -vs- UNIQUE INDEX Any input...?? The results are the same aren't they?? Dano
View Replies !
Unique Constraint
Hi, I have a table with two column, c1 and c2. c1 is set as primary key. I want c2 to be set with unique constraint. I choose this talbe in object explorer, right click and select modify. Then I choose "index/key" from "table designer" menu. The problem is that in the "index/key" dialog, the "Columns" item (under General) is always c1. if I click the "..." button to popup "index column", I could only choose either "c1" or <None> under "column name" dropdownlist. How could I choose c2 and set unique constraint on it? Thx Tao
View Replies !
UNIQUE Constraint
i'm just creating a table, no data as yet. i have a varchar(15) fld with Nulls NOT allowed i want to create a constraint that this field named ipkey be UNIQUE. I'm having trouble with the syntax for the constraint expression. i've typed in the SQL Server Managment Studio Express Expression field: UNIQUE(ipkey) and @unq_ipkey UNIQUE(ipkey) SQL Server Managment Studio Express complains: "Error validating constrating, do you want to edit expression" So - what am i doing wrong? And thanks for taking the time to help.
View Replies !
Unique Constraint Error When There Is No Constraint
We are using SQL CE 3.5 on tablet PCs, that synchs with our host SQL 2005 Server using Microsoft Synchronization Services. On the tablets, when inserting a record, we get the following error: A duplicate value cannot be inserted into a unique index. [ Table name = refRegTitle,Constraint name = PK_refRegTitle But the only PK on this table is RegTitleID. The table structure is: [RegTitleID] [int] IDENTITY(1,1) NOT NULL, [RegTitleNumber] [int] NOT NULL, [RegTitleDescription] [varchar](200) NOT NULL, [FacilityTypeID] [int] NOT NULL, [Active] [bit] NOT NULL, The problem occurs when a Title Number is inserted and a record with that number already exists. There is no unique constraint on Title Number. Has anyone else experienced this?
View Replies !
Sql Exception Unique Key Constraint
Hi All, I am trying to catch a specfic unique key constraint in a table. i my table i have two fields USERID And EMAILID and i set both to unique. now on registration form i am checking that USERID or EMAIID is already present or not. by taking ex.number =2627 i am not able to find which unique key constraint is getting violated. is there any other way to find it. thanks in advance.
View Replies !
Syntax For UNIQUE Constraint
I have an existing table:CREATE TABLE TestQuestions (qIdintNOT NULLIDENTITY(1,1)CONSTRAINTPK_TestQuestions PRIMARY KEY NONCLUSTERED,testIdintNOT NULLREFERENCESTests(testId)ON DELETE CASCADE,objectiveIdvarchar(30)NULLqTypeintNOT NULLCONSTRAINTDF_TestQuestions_qType DEFAULT (0),)And I'd like to add the constraint:ALTER TABLE TestQuestions ALTER COLUMN objectiveIdCONSTRAINT U_TestQuestions UNIQUE NONCLUSTERED (testId, objectiveId)Although obviously that's not the correct syntax. Could you tell mewhat *is* the correct syntax?Thanks,-- Rick
View Replies !
Unique Constraint With Conditioning?
Hi All: Is there a way to create a unique constraint with 4 fields combined, such as colA, colB, colC, and colD. but colD is for all the values but value = 3. If this does not work out, trigger probably is the last way I would like to approach. and it is slower than constraint with many inserts. thanks David
View Replies !
Can't Create Unique Constraint
I am attempting to create a unique constraint on an nvarchar field named theology (it is not the primary key field) that allows nulls. The table contains multiple rows with the value of null for field theology. The documentation says one can create a unique constraint on a field with all unique value except for null. Here is the error message: 'testtable1' table - Unable to create index 'IX_testtable1'. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found. Most significant primary key is ''. [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors. Any ideas? I am creating a unique constraint and not a unique index. Is there some other database option to set to allow this? .
View Replies !
Unique Constraint Problem In DWH
Hi, I have a table in OLTP DB which I synchronize every 1/2 an hour to my DWH. The records in the OLTP DB can be deleted, but, in the DWH they must be saved, so I have there an additional field IsDeleted (values 0/1). I have three columns in the original table, say: ItemID int (PK) ItemName int ParentID int I have unique index on the ItemName and the ParentID. The problem is that if an Item deleted in OLTP DB I change its IsDeleted field from 0 to 1 in DWH, now if it recreated in the OLTP DB it has a new ItemID but, the ItemName and ParentID are same as the deleted Item. When I try to insert the new Item to the DWH I getting unique constraint violation error because I didn't delete the old Item just changed its IsDeleted field from 0 to 1 when deleted. What I need is a way to make sure that I have uniqueness if the IsDeleted = 0, but, I can have many non-unique values when IsDeleted = 1. 10X
View Replies !
Unique Constraint And Null?
Can I create a unique constraint on a column that can contain null values? I need the control of the non null values (must be unique)...null is Ok if there's more than one. I tried creating a unique constraint but i'm getting the error (duplicate keys <null>)
View Replies !
Unique Constraint With Nulls?
Hi, I have a column in a table in SQL Server 2000 that needs to be unique while ignoring nulls. In other words, more than one record can have nulls, but all non-null values in that column must be unique. When I try to create a unique constraint on this column, the system complains that it can't do it because of duplicate data. The only thing that is duplicate are the nulls. It seems that SQL Server (2000, anyway) considers Nulls when enforceing uniqueness. Does anyone know how to get around this? Thanks!
View Replies !
Updating - Unique Key Constraint
I'm trying to clean-up certain Phone Types and I need to update Phone Types system wide, but certain constraints are holding me back...I'm just getting the error: Violation of UNIQUE KEY constraint 'IX_co_customer_x_phone'. Cannot insert duplicate key in object 'co_customer_x_phone'. The statement has been terminated. Strange thing is I can add certain statements to my where clause to find an exact customer and change their phone type, but when I want to change the phone type system wide I get this error. update co_customer_x_phone set cph_pht_key = '47961833-C53A-4223-8229-4453350934F7' FROM co_customer_x_phone (NOLOCK) LEFT JOIN co_phone_type (NOLOCK) on pht_key = cph_pht_key JOIN co_customer cst (NOLOCK) ON cph_cst_key = cst.cst_key JOIN co_phone (NOLOCK) ON cph_phn_key=phn_key where cph_pht_key='2E486DF9-7A16-47F6-83BA-A304746F50DE'
View Replies !
Add Unique Constraint Problem.
hi i create a sample table by this code and insert some values to it : create table test( c1 int, c2 int) insert test select 1,2 insert test select 2,44 insert test select 3,56 now, i want to add new column with unique constraint by this code : alter table test add c3 int unique but the following error has shown me : Msg 1505, Level 16, State 1, Line 1 CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.test' and index name 'UQ__test__2D27B809'. The duplicate key value is (<NULL>). Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors. The statement has been terminated. where does my problem and how to solve it ? thaniks
View Replies !
Unique Column Constraint
I like to use the management console to design tables, but have been unable to figure out how to add a unique column constraint. I have a primary key defined in col1 and want to make col2, char(10), unique, but keep col1 as the primary key. I have tried the Check constraint menu path, but do not know how to write the expression. If I go along the Indexes/Key path, I am forced to change the primary key which I do not want to do. Can anyone help? TIA, Joe
View Replies !
Unique / Check Constraint?
I have a table called tblImages with the following columns: ImageID [int] UserID [int] MainImg [bit] what i need to ensure is that only one MainImage can be = 1 (true) for each userId at any one time. any idea what i need to do? alex
View Replies !
Unique Constraint To A Column From Another Table
Is it possible to create a unique constraint to a column from anothertable? For example:tb_current:current_names--------------aaabbbtb_new:new_name--------cccNow I want to create a constraint on tb_new.new_name to be unique withrespect to tb_current.current_names. However, tb_new.new_name shouldnot be unique to itself. So I should not be able to insert 'aaa' totb_new.new_name. But I should be able to insert 'ccc' totb_new.new_name.Here's the script to reproduce this example:create table tb_current(current_names varchar(10))create table tb_new(new_name varchar(10))insert tb_current values ('aaa')insert tb_current values ('bbb')insert tb_new values ('ccc')select * from tb_currentselect * from tb_newinsert tb_new values ('aaa') -- this should NOT be allowedinsert tb_new values ('ccc') -- this should be allowed
View Replies !
Constraint Expression For Unique Keys
if i have a table which defines a rule as "combination of two fieldmust be unique", how can I write this in a constraint expressionsection?i started learning more about ms sql side to handle all the necessaryrules in back-end instead of front-end.also any good learning links, references, or book recommandations?thanks
View Replies !
Unique Constraint On Nullable Column
I have a producer table with a nullable column that stores SSN's. In some cases producers inherit SSN's from other producers. These records will have a null producer.ssn and a record stored in a child table to track the inheritance. Anyway, I've found two techniques to enforce uniqueness on a nullable column and wanted to get opinions as to which was better. First, write a trigger. Second, create a computed column that has a unique constraint on it. The computed column would use the SSN if not NULL Else use the PK identity value of the record. EXAMPLE DML:CREATE TABLE test ( ssn CHAR(9) NULL, testId INT identity(1,1) NOT NULL, ComputedConstraint AS CASE WHEN ssn IS NULL THEN CAST(testId AS CHAR(9)) ELSE ssn END, UNIQUE (ComputedConstraint)) Any comments would be greatly appreciated.
View Replies !
UNIQUE Constraint On VARBINARY Column
Hi, I have a table with one of its column VARBINARY(MAX). I want to make sure that the values in this VARBINARY(MAX) column is unique. SQL Server doesn;t allow to create Unique Constraint over VARBINARY fields - whats the best workaround for ensuring uniqueness on VARBINARY columns. Thanks, Loonysan
View Replies !
WHY DO FORIEGN KEYS HAVE TO BE UNIQUE OR HAVE A CONSTRAINT?
How do I go about protecting rows from deletion in this scenerio? Rule 1 The Administrator Users Account may not be deleted Rule 2 All Groups have Administrator as a member, and the Administrator cannot be removed. Rule 3 All Groups have the Administrators Group as a member, and the Administrators Group cannot be removed. Four tables: Users Table ( UID bigint Identity seeded with 1234 Primary key UserID varchar(30) NOT NULL UNIQUE ) INSERT FIRST RECORD (this record needs to be protected from deletion) UID = 1234 UserID='Admininstrator' INSERT FIRST RECORD (this record and others can be deleted) UID = 1235 UserID='Test User 1' Groups Table ( GID bigint Identity seeded with 1234 Primary key GroupName varchar(30) NOT NULL UNIQUE ) INSERT FIRST RECORD (this record needs to be protected from deletion) GID = 1234 UserID='Admininstrators' INSERT SECOND RECORD (this record and others can be deleted) GID = 1235 UserID='Test Group 1' Group_Members Table ( GID bigint NOT NULL //points to the group's ID and can't be unique UID bigint NOT NULL //points to the members UserID and can't be unique ) INSERT FIRST RECORD (this record needs to be protected from deletion because UID points to the Administrator) GID = 1234 UID = 1234 INSERT SECOND RECORD (this record and others can be deleted because UID does not point to the Administrator.) GID = 1234 UID = 1235 Group_Group_Members Table ( GID bigint NOT NULL //points to the group's ID and can't be unique GGID bigint NOT NULL //points to the group members GID and can't be unique ) INSERT FIRST RECORD (this record needs to be protected from deletion because GGID points to the Administrators Group.) GID = 1234 GGID = 1234 INSERT SECOND RECORD (this record and others can be deleted because GGID does not point to the Administrators Group.) GID = 1234 GGID = 1235 I have tried using foriegn keys, constraints an every thing else, but I hit a brick wall because FK requires the ke to be primary (btw is UNIQUE). Any help would be appreciated.
View Replies !
Using The Convert Function Within A Unique Constraint
In the code below I want the table to refuse inserts that are duplicate IDs, Users, Addresses and Days. InsertDateTime is a date which has precision to the second. I know I cannot use a primary key without creating another column that converts InsertDateTime to day precision. With the code below I get an error. Can convert not be used in a constraint? And if so, is there another way that I can do what I'm trying to do without creating a seperate column? ALTER TABLE exampletable ADD CONSTRAINT unique_submit UNIQUE (ID, User, Address, convert(varchar(),InsertDateTime,112))
View Replies !
Case-insensitive Unique Constraint?
I have a table in MSSQL 2005 Express that stores user data. I would like to maintain the cases of user names, but I need to insure that they are not duplicated using different cases. Is there a way that I can create a constraint to enforce this? Thanks, WBuik
View Replies !
Unique Constraint Based On Value In A Column
I don't immediately find if this is possible but hope someone can give me an answer: is it possible to make a unique constraint over 2 columns but only when 1 column has a specific value ? Example: table (tableid, instancetype, instancename, ..) instancetype can be A or B if it is A then instancename must be unique but for B instancename is not unique as these are copies from A only solution I can think of is to make a trigger on an insert to check what the instancetype is and do a select to see if the name already exists in the table or not.. are there other solutions to make a constraint like this ? Aeneas.
View Replies !
How To Create A Unique Constraint On Composite Columns
I am trying to create a Unique Constraint on a SQL Server 2005 table where the uniqueness is based on 2 columns. Could anybody provided some help on how I could enforce this on an existing table (link, or example) I have been looking around without luck. Thanks in advance John.
View Replies !
Inserts And Updates To A Table That Contains A Unique Key Constraint
I am looking for pros and cons for the following scenarios: When a table contains a unique key constraint is it viable to always do an insert and immediately check the @@ERROR value and if @@ERROR states a duplicate key exception then perform an update statement? Another possible solution would be to always check if the key exists and then do the insert / update based upon that result. This method will always require two steps.
View Replies !
Find Unique Key Constraint In Sql Server Mgmt
i just got an error in my application when i tried to do an insert stating that there was a "unique key constraint". I have the database open with sql server management studio and i need to find out how to view the constraints? How do i navigate to see the constraints on a table? Thanks
View Replies !
|
TRACKER
