I am new to MS SQL and I was wondering is it possible to create a table with unique rows?? By this I mean if a table has two columns then a duplicate row would be if BOTH columns matched two columns of another row.
Thanks
Hi all,I might be getting this all wrong but bear with me.
I need to create some kind of Unique field in my DB that is
nonsequential. This is because I need it to be difficult to guess
ids if you have an example in front of you.I have looked at
8digit EAN codes which include a check digit system.( I use a base digit of the row_id for these) Can anyone tell me
how many uniques I can get out of this system?For my ID: I have looked at something along the lines of:
Hex(row_id) + "T" + Hex( Trimmed(EAN)
)
The "T" serves to split the numbers for when I am converting back.
So for example:row_id EAN_code Hex(row_id) + "T" + Hex( Trimmed(EAN) )
------------------------------------------------------------------------------------------
3166 00031663 C5ET7BAF
3167 00031673 C5FT7BB9
3168 00031686 C60T7BC6
Is this too easy to guess (once you can tell there are two hex numbers there?)
What do people think?
Thanks,Pete
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 have a table where the key is an autonumber. I also have a field which holds the reference of a room eg 0BM1. It is nvarchar. Is there a way I can set this field to duplicates = No, so that my user cannot enter the same room reference more than once. Or do I have to do this check in my asp.net code ?
TIA
im running an INSERT statement and I need one of the fields (RecordID) to be automatically generated.
Its a unique identifier type.
INSERT INTO swdata.dbo.Reports_PI
(RecordID , SD1AverageSCResTime
)
VALUES
(,xxxxxxxxxxx
,@SD1AverageCallResolutionTime
,@SD2CountAllCalls
,@SD3PercentClosedIn24Hours
)
Is there a keyword or value I can put in xxxxxxxxx that will automiatically generate a unique identifier as the record is created?
Thanks,
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 1 Replies View RelatedHi there,I am trying to create a UID that is unique within my SQL Server. There are many users accessing the Server in seperate databases, but then I want to combine all the data from these tables, keeping the ID from each one as a primary key. I have written the following function, but when i call it as a default value for a field, it does not produce a unique number. CREATE FUNCTION GETNEXTID(@CURDATE DATETIME)RETURNS BIGINTASBEGINRETURN (SELECT CAST(CAST(DATEPART(YY,@CURDATE) AS VARCHAR) +RIGHT('0' + CAST(DATEPART(M,@CURDATE) AS VARCHAR),2) +RIGHT('0' + CAST(DATEPART(D,@CURDATE) AS VARCHAR),2) +RIGHT('0' + CAST(DATEPART(HH,@CURDATE) AS VARCHAR),2) +RIGHT('0' + CAST(DATEPART(SS,@CURDATE) AS VARCHAR),2) +RIGHT('00' + CAST(DATEPART(MS,@CURDATE) AS VARCHAR),3) AS BIGINT))END Can anyone help?
View 2 Replies View RelatedDoes 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
Hello.
Could anyone tell me why it is not possible to create a foreign key on two columns those references on 2 columns in another table? Those 2 columns have each a unique constraint.
I have:
CREATE TABLE T_PK (ID1 INT CONSTRAINT CHK_UNIQUE1 UNIQUE,ID2 INT CONSTRAINT CHK_UNIQUE2 UNIQUE)
CREATE TABLE T_FK (ID1 INT, ID2 INT)
And I want to do:
ALTER TABLE T_FK ADD CONSTRAINT CHK_FK FOREIGN KEY (ID1, ID2) REFERENCES T_PK (ID1,ID2)
I see no reason why this is not working because always
a row in the table T_FK referencing only one row in table T_PK.
Thank you.
Have a nice day.
why it is not possible to create a Foreign key to a Unique constraint?
Table A has column 1 holding a Primay key and two columns (2 and 3) holding a Unique combination (and some more columns).He created an Unique constraint on column 2 and 3 together.
He wanted to use this Unique combination to point to table B (instead of the table 1's PK) so he tried to create a foreign key on a column in table B but an error popped up prompting;
The columns in table 'TABLE_A' do not match an existing primary key or UNIQUE constraint.
Ok - these two columns ar no PK but the hold an Unique constraint......
Introduction
This MS SQL Store Procedure solves a problem which is not common
except when you have a table in a database which each row needs
to be uniquely identified and their are more rows in the table
than you can identfy with a big int or Unique Identifier.
So for example,
if you used a "unique identifier" you would be limited to
8.6904152163272468261061026005374e+50 unique rows at best.
If you used a "Big Int" you would be limited to -2^63 (
-9223372036854775808) through 2^63-1 (9223372036854775807).
This method will allow you to have 2.2528399544939174411840147874773e+106. (With cluster indexing the
identity field.)
or, 4.722366482869645213696e+129 (Without indexing the identity field)
Why would you need that many unique values? Well, the reason for
this invention is due to the need to track every email that an
application sends for Sarbanes/Oxley Requirements. With this
technique, every email sent out will be uniquely identified for a
very very very long time.
The purpose of this is to show how to set up an identity column with
a larger range than a big int or unique id. Try transaction logs
where you need to track every change, or determining click paths
through a website, etc.
The point is, that this method pretty much does what the title
says, "Create unlimited Unique ID's". What table you apply this too
and for what reason is up the the programmer.
Background
This design uses basic counting methods and handles the limitations
of MS SQL TSQL. First, you can use a varchar(4000) as the unique id
column but the issue with this is that as of MSSQL 2000, the largest
indexable field is 900 character. So if you need to be able to
quickly search the table by key, or clustered keys, you need to limit
your key column with to 900 characters, otherwise if you use a varchar(4000) make sure when searching the table you create a
temporary table, select the subset into it and search that.
Using the code
First, copy and paste all the TSQL into a Query Window and
compile it in the database you wish to use it in.
[Code]
/**********************************************************************************
Program: ALTER Unlimited Unique ID's (Auto Increment)
Programmer: Vince Gee
Date: 9/28/2005
Parameters:
@TABLE_NAME - The name of the table to establish the auto incrementing field in
@COLUMN_NAME - The column name in the table to establish the auto incrementing field in
@DROP_EXISTING_TRIGGER - Whether or not on running to drop an existing trigger with
the same name.
Theory:
A varchar 900 field will be able to have 2.2528399544939174411840147874773e+106 unique identifiers in it.
A uniqueID only has 8.6904152163272468261061026005374e+50 unique identifiers in it.
Description:
The purpose of the sql procedure is to automate the creation of
auto updating identities on a sql table without the trouble of
writing the trigger each time.
So what does this do? Well for example lets say we have the following
table which you will have many many many rows in.
ALTER TABLE [Countertest]
(
[myKey] [varchar] (900) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[anyvalue1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[anyvalue2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AA] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
myKey is the unique identifier for each row. We can set it's size really
to anything, This proc will look for the column specified and determine it's
size. The column should be nvarchar of type
All the other columns don't matter, the only issue is if all the column names concated
together exceed the storage compacity w/ the trigger code of 4000 characters. If this
is the case your gonna have to write the trigger manually.
So to set the auto incrementing field up you would call this proc:
Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey'
or
Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey',1
Output:
When data is inserted into the table, the auto incrementing field will look like
0000000001
0000000002
0000000003
0000000004
0000000005
0000000006
0000000007
0000000008
0000000009
000000000A
000000000B
000000000C
000000000D
000000000E
000000000F
000000000G
000000000H
000000000I
000000000J
000000000K
000000000L
with how many 0's set up etc. It goes 0-9, then A-Z
***********************************************************************************/
ALTER PROC SP_SET_UNIQUE_FIELD
@TABLE_NAME VARCHAR(255),
@COLUMN_NAME VARCHAR(255),
@DROP_EXISTING_TRIGGER BIT =0
AS
DECLARE
@EXECSQLSTRING nvarchar (4000),
@counter int,
@COLUMN_NAMES varchar(4000),
@tCOLUMN_NAME varchar(4000),
@MAXORDINAL int,
@KEYLENGTH int
--If the trigger
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[' + 'IO_Trig_INS_' + @COLUMN_NAME + ']') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
begin
IF @DROP_EXISTING_TRIGGER = 0
BEGIN
-- USER DOESN'T WANT US TO AUTODROP THE TRIGGER, BY DEFAULT AUTODROP TRIGGER IS OFF
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
PRINT 'STOP ERROR :: PLEASE DROP THE EXISTING TRIGGER BEFORE RUNNING THIS PROC'
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
RETURN
END
ELSE
BEGIN
--CREATE A SQL STRING TO DROP THE TRIGGER SO WE CAN RECREATE IT.
set @EXECSQLSTRING = 'drop trigger IO_Trig_INS_' + @COLUMN_NAME
--EXECUTE THE SQL
EXEC SP_EXECUTESQL @EXECSQLSTRING
END
end
--CREATE A TABLE TO HOLD THE RESULTS FOR THE SP_COLUMNS
create table #temp
(
TABLE_QUALIFIER varchar(255),
TABLE_OWNER varchar(255),
TABLE_NAME varchar(255),
COLUMN_NAME varchar(255),
DATA_TYPE int,
[TYPE_NAME] varchar(255),
[PRECISION] int,
LENGTH int,
SCALE int,
RADIX int,
NULLABLE int,
REMARKS varchar(255),
COLUMN_DEF varchar(255),
SQL_DATA_TYPE int,
SQL_DATETIME_SUB varchar(255),
CHAR_OCTET_LENGTH int,
ORDINAL_POSITION int,
IS_NULLABLE varchar(255),
SS_DATA_TYPE int
)
--POPULATE THE TEMP TABLE W/ A SP_COLUMNS ON THE TARGET TABLE
insert into #temp
exec sp_columns @TABLE_NAME
--CYCLE THROUGH ALL THE COLUMN NAMES AND BUILD OUR COLUMN NAME STRING
--FOR INSERTS. THE LAST COLUMN NAME IS ALWAYS THE IDENTITY FIELD.
SELECT @MAXORDINAL = MAX(ORDINAL_POSITION) FROM #TEMP
SET @COUNTER = 1
SET @COLUMN_NAMES = ''
WHILE @COUNTER <= @MAXORDINAL
BEGIN
select @tCOLUMN_NAME = COLUMN_NAME FROM #TEMP WHERE ORDINAL_POSITION = @COUNTER
if (@tCOLUMN_NAME <> @COLUMN_NAME)
begin
SET @COLUMN_NAMES = @COLUMN_NAMES + @tCOLUMN_NAME+ ','
end
else
begin
select @KEYLENGTH = LENGTH FROM #TEMP WHERE ORDINAL_POSITION = @COUNTER
end
SET @COUNTER = @COUNTER +1
END
--CLEAN UP
drop table #temp
IF @KEYLENGTH > 900
Begin
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!WARNING:: YOU WILL NOT BE ABLE TO INDEX THIS TABLE BY YOUR CHOSEN COLUMN,!!!!!!!!!!!!!!!!!!!!!'
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!BECAUSE THE COLUMN IS OVER 900 CHARACTERS. 900 CHARS ARE THE MAX THAT !!!!!!!!!!!!!!!!!!!!!'
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!THAT CAN BE INDEXED !!!!!!!!!!!!!!!!!!!!!'
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
END
SET @EXECSQLSTRING = '
CREATE TRIGGER IO_Trig_INS_' + @COLUMN_NAME + ' ON ' + @TABLE_NAME + '
INSTEAD OF INSERT
AS
BEGIN
DECLARE
@VALUE VARCHAR(' + CONVERT(VARCHAR(900),@KEYLENGTH) + '),
@REVERSED_VALUE VARCHAR(' + CONVERT(VARCHAR(900),@KEYLENGTH) + '),
@COUNTER INT,
@LEFT_SIDE VARCHAR(' + CONVERT(VARCHAR(900),@KEYLENGTH) + '),
@RIGHT_SIDE VARCHAR(' + CONVERT(VARCHAR(900),@KEYLENGTH) + '),
@CHAR_VALUE CHAR
select @VALUE = ISNULL(MAX(' + @COLUMN_NAME + '),REPLICATE (' + "'0'" + ',' + CONVERT(VARCHAR(900),@KEYLENGTH) + ') ) from ' + @TABLE_NAME + '
SET @REVERSED_VALUE = REVERSE(@VALUE)
SET @COUNTER = 1
WHILE @COUNTER <= LEN(@REVERSED_VALUE)
BEGIN
SET @CHAR_VALUE = SUBSTRING(@REVERSED_VALUE,@COUNTER,1)
IF ASCII(@CHAR_VALUE) <> 122
BEGIN
IF @COUNTER = 1
SET @LEFT_SIDE = ''''
ELSE
SET @LEFT_SIDE = LEFT (@REVERSED_VALUE,@COUNTER - 1)
IF @COUNTER = LEN(@VALUE)
SET @RIGHT_SIDE = ''''
ELSE
SET @RIGHT_SIDE = RIGHT (@REVERSED_VALUE,LEN(@REVERSED_VALUE)- @COUNTER)
IF ASCII(@CHAR_VALUE) + 1 = 58
SET @CHAR_VALUE = CHAR(97)
ELSE
SET @CHAR_VALUE = CHAR(ASCII(@CHAR_VALUE) + 1)
SET @REVERSED_VALUE = ISNULL(@LEFT_SIDE,"") + ISNULL(@CHAR_VALUE,"") + ISNULL(@RIGHT_SIDE,"")
BREAK
END
ELSE
BEGIN
IF @COUNTER = 1
SET @LEFT_SIDE = ''''
ELSE
SET @LEFT_SIDE = LEFT (@REVERSED_VALUE,@COUNTER - 1)
IF @COUNTER = LEN(@VALUE)
SET @RIGHT_SIDE = ''''
ELSE
SET @RIGHT_SIDE = RIGHT (@REVERSED_VALUE,LEN(@REVERSED_VALUE)- @COUNTER)
SET @CHAR_VALUE = CHAR(48) --SET THE CURRENT POSITION TO ZERO AND WE INCREMENT THE NEXT DIGIT.
SET @REVERSED_VALUE = ISNULL(@LEFT_SIDE,"") + ISNULL(@CHAR_VALUE,"") + ISNULL(@RIGHT_SIDE,"")
END
SET @COUNTER = @COUNTER +1
END
SET @VALUE = REVERSE (@REVERSED_VALUE)
INSERT INTO ' + @TABLE_NAME + ' (' + @COLUMN_NAMES + @COLUMN_NAME + ')
SELECT
' + @COLUMN_NAMES + '@VALUE
FROM
inserted
END'
if len(@EXECSQLSTRING) <4000
begin
EXEC SP_EXECUTESQL @EXECSQLSTRING
end
else
begin
print 'STOP ERROR:: BUFFER OVERFLOW. THE GENERATED TRIGGER TEXT > 4000, Trigger must be hand written.'
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
First, to test the functionality create a temp table.
First, to test the functionality create a temp table.
Create TABLE [Countertest]
(
[myKey] [varchar] (900) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[anyvalue1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[anyvalue2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AA] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
Second, call the proc on the table. Parameters are:
Table Name - Name of the table to put the trigger on.
Column Name - Name of the column to use as the key.
Drop Existing Trigger - If this is set to 1 and a trigger with the name generated by this stored procedure exists it will drop it. (OPTIONAL)
Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey'
or
Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey',1
Now, we are going to test how this works. Copy and paste the following code into a query analyzer.
declare @t int
set @t = 0
while @t <= 40000
begin
insert into countertest
select '','s','s','s'
set @t = @t + 1
end
GO
Once this completes, you can inspect the unique id by selecting it from the table
SELECT RIGHT (MYKEY,10) FROM countertest
The table will keep incrementing the key column first 0-9 then a-z. When it reaches all 'z' it will roll over, but if your key column is wide enough this shouldn't happen while your still employeed.
Also, the stored procedure figures out how wide the key column is automatically and adjusts the script accordingly. So if you widen or shrink the key column, just rerun the proc to update the trigger script.
Hi, can anyone guide me on how to create a unique field with SqlExpress and VC#.net?
I know how to create primary key with identity, however I need to create another unique column for "email". I have search high and low for a solution but found none, the closest i got is unique constraint which I've not idea what's that.
Please help, else I have to always do a duplication check before inserting new records.
Many thanks.
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?
I have created a view based on joining 3 tables, however, it is not possible to have a unique field in the view which I must need it and I must create index on some other fields. Is there any way to create sequence number or uniqie field in mssql view.
View 13 Replies View RelatedI 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.
I'm not able to create indexed views,
which are clustered-indexed on only 1st field.
I can't even INCLUDED other fields.
I need the entire view to exist as a physical table. (for performance)
Please let me know the work around.
Thanks..
Hi all,
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.
Any help would be appreciated.
Thanks,
Amir
Hi all,My program is a central data processing application built in ASP.We have different companies that use different web pages on another webapplication (from different countries) to load some inventory data(merchandise - clothes).Here is my requirement: Load different types of data (in differentformats) into a common set of tables, to do this I have to firstfilter, do lookup's, use cross-reference tables on this data and thenload it into a couple of tables.Since data is so different everytime, I want to have one main storedprocedure in which I can build the table (I know the format of thisdata so I know what columns/types to create) into which I will feed thedata.After this I will create other stored procs/udf's that reference thistable (probably from the same main stored proc) 'cleanse' the databefore loading into the actual tables.How feasible is my approach? (if you can call it one :-) I do not wantto have seperate tables for each country, that will be too many and sothis plan...If not any other ideas will be really helpful.thanks much
View 3 Replies View RelatedNeed help,
I'm a newby to SQL and looking for how-to-help.
I have an existing DB and within a certain table, I have created a new Column via Studio manager, but need help with the following:
Need to make the new Col "Unique, and Indexed" but cannot see anywhere in studio manager interface to do it.
Thanks.
I have the following table (Table does not have unique key id )
Last Name First Name DATE Total-Chrg
Jaime KRiSH 5/1/2015 -4150.66
Jaime KRiSH 5/1/2015 1043.66
Jaime KRiSH 5/1/2015 1043.66
Jaime KRiSH 5/1/2015 4150.66
Jaime KRiSH 5/3/2015 4150.66
Peter Jason 5/1/2015 321.02
Peter Jason 5/1/2015 321.02
Peter Jason 5/23/2015 123.02
I want the results to be in following way
Uniq ID Last Name First Name DATE Total-Chrg
1 Jaime KRiSH 5/1/2015 -4150.66
2 Jaime KRiSH 5/1/2015 1043.66
2 Jaime KRiSH 5/1/2015 1043.66
3 Jaime KRiSH 5/1/2015 4150.66
4 Jaime KRiSH 5/3/2015 4150.66
5 Peter Jason 5/1/2015 321.02
6 Peter Jason 5/1/2015 321.02
7 Peter Jason 5/23/2015 123.02
May be we may do by dense_rank or Row_Number, but I couldn't get the exact query to produce based on the above table values. There are some duplicates in the table(which are not duplicates as per the Business). For those duplicated Unique ID should be same(Marked in Orange Color which are duplicates).
I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.
View 4 Replies View Related
Hi,
Te following situtation is :
ReportModel is created ,there is only a named query in DSV ,it has a few tables in it(The relationship are inner joins and outer joins).
The question is how could I create a unique logical primary key to identify each unique row in the named query dataset, and also you cannt generate a model unless the named query has a logical primary key . how can I solve this problem,any help?
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
This index is not unique
ix_report_history_creative_id
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
The statement has been terminated.
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.
View 8 Replies View RelatedHelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil
View 5 Replies View RelatedWhat'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?
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
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 2 Replies View Related
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 ?
I am having a problem trying to figure out the best way to get the results I need. I have a table of part numbers that is joined with a table of notes. The table of notes is specific to the part number and user. A row in the notes table is only created if the user has entered notes on that part number. I need to create a search that grabs all matches on a keyword and returns the records. The problem is that it currently returns a row from the parts table with no notes and a separate row with the notes included if they had created an entry. It seems like this should be easy but it eludes me today.
Here is the code
Code Snippet
create procedure SearchPartKeyword
(
@Keyword varchar(250) = null,
@Universal_Id varchar(10) = null
)
as
select p.PartNumber, p.Description, p.ServiceOrderable, n.MyNotes, p.LargestAssembly, p.DMM,
p.Legacy, p.Folder, p.Printer
from Parts p inner join notes n on p.PartNumber = n.Identifier
where n.Universal_ID = @Universal_ID and p.Description like @Keyword
union
select p.PartNumber, p.Description, p.ServiceOrderable, '' as MyNotes, p.LargestAssembly,
p.DMM, p.Legacy, p.Folder, p.Printer
from Parts p
where p.Description like @Keyword
and the results:
PartNo Description SO Notes LA DMM Legacy Folder Printer
de90008 MAIN BOARD 1 DGF1 114688 0 0 0
de90008 MAIN BOARD 1 I love this part Really I do DGF1 114688 0 0 0
This could return multiple part numbers and If they have entered notes I want the row with the notes
Thank You
Dominic Mancl
What 's difference between Unique key and unique index in SQL server 2005?
View 9 Replies View RelatedHere 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