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?
I have a problem. I have two feilds in a table. Both of them are (int). I want to be able make sure the same number is not put in both columns for each row. Both colums have to be a different number, and also if one is null, the other cannot be. How do I go about enforcing this policy?
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.
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?
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?
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 ?
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?
I have 3 columns that I'd like to set as a key, however I do have multiple instances of NULLs for all 3 columns. Thus when I try to create the key, it stops because there are multiple instances of NULLs. I looked at using procedure that check each entry made into the table, but I couldn't figure out how to deny an INSERT/UPDATE request. Anyone have any better methods of implimenting the unique key?
Hi, I have a non-cluster index on the filed, I need to make it non-cluster index and unique which allows multiple nulls, how should I do this with an sql statement?
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.
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?
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))
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.
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?
I want to add a unique constraint on 3 fields, to only allow the value in the field ONE time. The value will NEVER be the same for anything else. This is the table structure
Code: Create Table Employees ( P_Id int NOT NULL, InstructorName varchar(255) NOT NULL, CourseName varchar(100) NOT NULL, DataTableName varchar(100) NOT NULL )
I want to create a unique constraint across the fields Instructorname, CourseName, DataTableName as their is ONLY 1 instructor per course per table so those 3 fields will ALWAYS hold unique values. I think the constraint syntax would go like so, but want to check before I go butchering some sql syntax.
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
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
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'
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
