Ensuring Unique Primary Key

Jan 23, 2008



For our database application we get our primary keys by calling a stored procedure that pass's in the table name and outputs the next primary key number assigned to that table.



ALTER procedure [dbo].[sp_getNextKey]

@TableName char(100),

@NextKey T_ID output

as

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

begin transaction

select @NextKey = NextKeyValue from T_KeyGenerator where TableName like @TableName

update T_KeyGenerator set NextKeyValue = NextKeyValue + 1 where TableName like @TableName

commit transaction


I then take that primary key, and other data, and insert that into the desired table.

The Problem I am having is that my user's keep getting "cannot insert duplicate key in table".
So I assume that I do not have the transaction set right, or missing something.
I need it to lock the row in the t_keyGenerator table so that no other users can view that row until I update it with the new value and commit the transaction.

Any help would be greatly appreciated

View 8 Replies


ADVERTISEMENT

UNIQUE But Not PRIMARY

Oct 20, 2006

Hello
for MS SQL 2000 I want to CREATE a Table as follow :

CREATE TABLE [dbo].[Local] (
[id_Local] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar] (100) NOT NULL,
[Info] [nvarchar] (100) NULL
)


id_Local is PrimaryKey autoincrement
but Name must be UNIQUE (not 2 times the same name)

how can script it ?

thank you

View 2 Replies View Related

Primary Key Vs Unique Key

Aug 27, 2007

Hi,

Can anyone tell what are the specific scenarios where Unique key is recommened over primary key ?
While designing a database table in what all cases we should think about going for Unique key rather than a primary key.

Regards,
Amit

View 6 Replies View Related

Difference Between A Primary Key And A Unique Key?

Jan 19, 2008

 hello all sorry m asking Database Question .. m talking about SQL SERVER 2005  but i have bit confusion...  what difference between a
primary key and a unique key?  if m not wrong  then primary key doesn't allow NULLs, but
unique key allows  NULLs but in many searches comes that primary key doesn't allow NULLs, but
unique key allows one NULL only. what this means 'one NULL only'?? according to my practical we can give more then 1 nulls... plz clear this point???

View 1 Replies View Related

Generate Unique Primary Key

Aug 17, 2007

Is there any way to auto generate the unique primary key when inserting data in MS SQL?
there is a way to create the increasing integer as primary key, but is there any ways other than that?

thank you

View 5 Replies View Related

UNIQUE/PRIMARY KEY Confusion.

Nov 9, 2006

I wrote this stored procedure and it works fine, it seems. The questions I have are as follows:
(1) What is the difference between PRIMARY KEY and UNIQUE. They seem to pertain to the same behavior. When I used only UNIQUE as qualifier I did not see that the column was marked as primary in the SQL Management Studio.
What I need is for a column to be unique in the sense that it would not allow duplicate values and it must have an INDEX on it. I need it to be descending.
(2) Did I do it right or there are aspects in here I do not quite see?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[CreateTableDailyClose]
@symbol varchar (10) = null
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL VARCHAR(500)
SET @SQL = 'CREATE TABLE dbo.dailyCl_' + @symbol + ' (
dateTimed DateTime NOT NULL PRIMARY KEY,
opened float NULL,
high float NULL,
low float NULL,
closed float NULL,
volume int NULL,
adjClosed float NULL
)'
EXEC sp_sqlexec @Sql
SET @SQL = 'CREATE UNIQUE INDEX dateTimed ON dbo.dailyCl_' +
@symbol + ' (dateTimed DESC) '
EXEC sp_sqlexec @Sql
END

Thanks.

View 16 Replies View Related

Unique Index On X Columns But Not Primary Key

Feb 23, 2006

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 5 Replies View Related

Question On Primary Key, Unique Index

Oct 8, 2007



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 5 Replies View Related

Transact SQL :: Need Of Primary Key If Everything Can Be Achieved By Unique Key?

Oct 13, 2015

What is the need of Primary Key if everything can be achieved by Unique Key? Everything that primary key does, Unique key can also fill those things.Why primary key is required?

View 4 Replies View Related

How To Return Primary Unique Index Key On Insert

May 27, 2002

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 6 Replies View Related

Unique Colm Indexes And Primary Keys

Jan 25, 2006

I have a deal table, each of these investments must be unique. I created a int pk : idDeal. Does that make sense or should i just use the deal colm being it has a unique constraint,
Reguarding indexes, should i make the auto # colm my pk and make that the clustered index? and put another index on the Deal Colmn? Any suggestions welcomed

Thank you

View 4 Replies View Related

Lookup Primary Key Of A Master Table Which Is Does Not Contains Unique Key

Mar 21, 2008



Hi
I have a strange requirement in ETL operation. My Source contains only the details table data. Out of it, I have to load the master table and refer the Master table primary key ID to load Details table. I can easily load the Master Table with aggregate transformation. But problem is how to look up the Master Table Primary Key ID to load the Details table, as the master does not contains any unique key to lookup. This may seems to be strange but this is my requirement. You can refer the Source and Destination data model as below. They may give you clear picture. Can you guys help me out on this?

My Source Table
--------------------------








Citye Type
City Name

Source
New York

Via City1
Pittsburg

Destination City
Chicago

Source
New York

Via City1
Philadelphia

Destination City
Chicago


My Destination
------------------------






Master Table

ID

1

2









Detail Table



Master Table ID
Citye Type
City Name

1
Source
New York

1
Via City1
Pittsburg

1
Destination City
Chicago

2
Source
New York

2
Via City1
Philadelphia

2
Destination City
Chicago


Thanks

View 11 Replies View Related

Report Model - How To Create A Unique Logical Primary Key In A Named Query

Apr 7, 2008



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?


View 2 Replies View Related

Ensuring All Connections Are Closed.

Feb 22, 2007

In a previous post I asked the question relating to moving a file assoicated with a connection. It appears I need to close the connection first.

 

On advice, in a script task I created the following:

 

 

Dim dtsConnection As Microsoft.SqlServer.Dts.Runtime.ConnectionManager

For Each dtsConnection In Dts.Connections

Dim LiveConnection As Object = dtsConnection.AcquireConnection(Nothing)

Dts.Events.FireInformation(0, "", "Connection Name : " + dtsConnection.Name, "", 0, False)

dtsConnection.ReleaseConnection(LiveConnection)

dtsConnection.Dispose()

Next

Dts.TaskResult = Dts.Results.Success

 

RetainSameConnection is set to true. 

 

The dispose line is something I've added just to try - I've tried it without this line as well.

The next component then moves the file and fails complaining that the file is in use.

What can I do?

 

Regards

 

Guy

 

View 2 Replies View Related

DB Engine :: How To Convert Unique Clustered Index Into Clustered Primary Key To Use With Change Tracking

Sep 4, 2015

We are going to use SQL Sever change tracking. The problem is that some of our tables, which are to be tracked, have no primary keys. There are only unique clustered indexes. The question is what is the best way to turn on change tracking for these tables in our circumstances.

View 4 Replies View Related

Ensuring That Matrix Data Region Col Headers Are Sorted

Dec 25, 2007

i ran a preview of a matrix based report whose column headers are dates. The dates seem to be displaying in a somewhat (not completely) random order from left to right. How can I ensure that they display chronologically from left to right?

View 1 Replies View Related

Ensuring SOAP Request Success/Failure Notification

May 2, 2007

More of a general SOAP service call question.



Does anybody have any experience/advice on how to ensure that SOAP service call success/failures are returned to the calling app?



Consider a client that calls a SOAP service during which the client goes down and is unable to receive the SOAP response, the work having been done by the service. Similarly, the SOAP service may perform the task but a failure in the return makes the client think the process failed.



What would be the best way to ensure that the client is notified to avoid the call having to be made again?



Are there middleware tools that can be used to provide a form of message queuing for SOAP service calls?



Thanks



View 1 Replies View Related

Composite Primary Keys Versus Composite Unique Indexes

Feb 20, 2007

Hello,

I have a table which has a composite primary key consisting of four columns, one of them being a datetime called Day.

The nice thing afaik with this composite key is that it prevents duplicate entries in the table for any given day. But the problem is probably two-fold

1. multiple columns need to be used for joins and I think this might degrade performance?
2. in client applications such as asp.net these primary keys must be sent in the query string and the query string becomes long and a little bit unmanagable.

A possible solutions I'm thinking of is dropping the existing primary key and creating a new identity column and a composite unique index on the columns from the existing composite key.

I would like to have some tips, recommendations and alternatives for what I should do in this case.

View 1 Replies View Related

Ensuring Database Is Updated In Mobile Device When Performing Merging

Jul 24, 2007

Hi,

I am using web service to synchronize data between SQL Server 2005 on a desktop and a SQL Server Mobile Edition on a mobile device.

Initially, when i added the data source to my mobile application, my .sdf only has 3 records. I am then supposed to retrieve 97 more records from the desktop server when my mobile application runs. After performing merging, I clicked (on my device emulator) on the .sdf deployed under my mobile application and the query analyzer showed all 100 records.

Does this mean my mobile database has been successfully updated with all 100 records? If not, how do I check that? The reason why I am asking this is that the file size of this .sdf with 100 records is still the same as the initial .sdf with only 3 records. How is this possible?

Am I missing anything or getting some concepts wrong? Please enlighten. Thanks.

View 4 Replies View Related

T-SQL (SS2K8) :: Convert Non-cluster Primary Key To Cluster Unique

Nov 6, 2014

I have following script which i am planning to run to drop all non-clustered primary keys on a database and then created as clustered. I am using someone else's script so don't know how to modify this. Some of primary key columns are used in references in other tables.

is there anyway i can drop the existing primary keys and using their original script then create again as clustered including restoring all foreign and reference keys and unique or no unique.

DECLARE @table NVARCHAR(512), @tablename NVARCHAR(512),
@sql NVARCHAR(MAX), @sql2 NVARCHAR(MAX), @sql3 NVARCHAR(MAX),
@column NVARCHAR(MAX);
DECLARE @indexname NVARCHAR(512);
SELECT name As 'Table'

[Code] ....

View 8 Replies View Related

SQL Server 2008 :: Techniques For Ensuring Isolated Temp Table Scope?

Jul 28, 2015

what are some common techniques for ensuring an isolated temp table scope? For example, if 2 different sprocs happen to crud a temp table with the same name? I'm guessing that big SQL shops establish a standard for this early on to avoid conflicts between sprocs.

View 3 Replies View Related

SQL Server Admin 2014 :: Ensuring Upgrade Advisor Is Running Properly?

Oct 30, 2015

I have a SQL Server 2008 instance that is running on "LiveServer" our production database (ProdDB) - and we need to upgrade to 2014. In order to do some upgrade testing, I spun up a VM with the same version of SQL server on the test VM (TestServer), did a backup of the production DB from the live server, and restored it to TestServer under a different name (ProdDBUA).

I then installed SQL2014 Upgrade advisor onto TestServer, and ran it, checking all the boxes (reporting services etc..) and it all came back clean - no issues whatsoever - not a single warning even. I'm under the impression that stored procs/functions etc... all reside within the DB, so a backup will include those. Is that correct?

The problem is, I know I have stored Procs, functions and views that use deprecated joins in that LiveServer.ProdDB. What do I need to do/configure/check in order to make sure that the Upgrade Advisor is actually checking through all that T-SQL that has deprecated code? I want to have a list to give to my report writers of procs/functions/views that need to be rewritten prior to the upgrade going live.

If there is a modification that needs to be run on the TestServer.ProdDBUA, a cursor to change the path etc. DB is running in Compatibility mode 90.

View 4 Replies View Related

SQL Server 2012 :: Failing On Update With Unique Index Error (Not Unique)

Jul 5, 2015

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.

View 6 Replies View Related

What Is The Difference Between A UNIQUE INDEX And A UNIQUE CONSTRAINT?

Sep 22, 2004

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 Related

Unique Constraint Vs Unique Index In MS SQL 2000

Jul 20, 2005

HelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil

View 5 Replies View Related

Unique Constraint And Unique Index, What's The Difference?

Jun 24, 2006

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 1 Replies View Related

Unique Index Vs Unique Constraint

Mar 7, 2001

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 4 Replies View Related

Unique Constraint Vs Unique Index

Jan 20, 2006

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

Unique Index Vs Unique Constraints

Mar 26, 2008



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 12 Replies View Related

How To Select Unique Row When Entire Row Not Unique?

Mar 12, 2008

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

View 1 Replies View Related

What 's Difference Between Unique Key And Unique Index

Nov 13, 2007

What 's difference between Unique key and unique index in SQL server 2005?

View 9 Replies View Related

Convert Composite Primary Key Into Simple Primary Key

Jan 11, 2007

Uma writes "Hi Dear,
I have A Table , Which Primary key consists of 6 columns.
total Number of Columns in the table are 16. Now i Want to Convert my Composite Primary key into simple primary key.there are already 2200 records in the table and no referential integrity (foriegn key ) exist.

may i convert Composite Primary key into simple primary key in thr table like this.



Thanks,
Uma"

View 1 Replies View Related

Adding Primary Key To A Table Which Has Already A Primary Key

Aug 28, 2002

Hi all,
Can anyone suggest me on Adding primary key to a table which has already a primary key.

Thanks,
Jeyam

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved