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.
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.
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 ?
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
I am trying to add a unique index/constraint on a column that allows NULL values. The column does have NULL values and when I try to create a unique constraint, I get the following error.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 9. Most significant primary key is '<NULL>'.
Are'nt you allowed to create a UNIQUE constraint on a NULL column? Books Online says that you are allowed to create a unique constraint on NULL columns, then why am I getting this error.
I want to enforce a unique constraint on a column which must be encrypted in MSSQL 2005 using Cell Level Encyption (CLE).
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'itsaSECRET!!!3£3£3£!!!' CREATE CERTIFICATE ERCERT WITH SUBJECT = 'A cert for use by procs' CREATE SYMMETRIC KEY ERKEY WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE ERCERT
[Code] ....
The output makes it obvious why the constraint has 'not' been enforced.
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?
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'