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


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

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 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

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

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

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 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

DTS Confusion...

Nov 28, 2001

I'm using DTS for the first time and am having difficulty understanding some of its usage.
In essence, I have 70 tables which are on an ORACLE database. I've created the schema on SQLServer from an ERWin model. I now wish to use DTS to import the data into SQLServer. Of course there are referential integrity constraints. The DTS 'import wizard' seems oblivious of these and carries out the import in alphabetical order!
I'm now trying the DTS designer (which I don't fully understand yet). It appears that I have to create a workflow which moves the workflow from table to table in the order dictated by RI - this is not a trivial task! (I want point and click!!).
Are there any shortcuts I can take to have DTS automatically arrange the workflow in the correct order? Can I do it programaticaly? I don't want to have to fiddle with 140 'workflow' and 'success' links.
Cheers.

View 1 Replies View Related

Confusion In AWE

Apr 26, 2007

suppose i have enabled only AWE option in sql server but i didn't add 3

GB switch in boot.ini. in this case what will be the effect on physical memory?

View 9 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

SQLExpress ASP.NET Confusion

Jul 19, 2006

Hello,I am just getting reaquainted with programming and all. I work in GIS mapping and am having diffilculty using  a ASP.net internet Map site and adding records to a needed database.I would like to be able to add records to a linked SQLExpress table when the user clicks on a map location. I adapted the following code snippet from an example that does what I would like only using an .mdb file.CODE:    // create connection to database                        var connection;                        connection = Server.CreateObject("ADODB.Connection");                        connection.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OutdoorsExp;Data Source=NOLOGOSQLEXPRESS;");                        status = "Can't access 'FishSQL' table.";                        // create recordset                        var recordset;                        recordset = Server.CreateObject("ADODB.Recordset");                        recordset.CursorType = 1                        recordset.LockType = 3;                        recordset.Open("dbo.FishTable", connection);                                                status = "Can't append new record.";                        // append record for clicked location                        recordset.AddNew();                        recordset.Fields("LongitudeI").Value = xco;                        recordset.Fields("LatitudeI").Value = yco;                                                recordset.Update();                        recordset.Close();                        // release the .mdb file                        connection.Close();However, this does not add anything to the SQLEx table. What am I missing? I am thinking that it might have something to with the primary key.I had a table with way more columns then just lat/longs (as above) but only coded it to add data to the latitude and longitude fields. Nothing got added of course. Would have to add data to every field that doesnt allow nulls.How do I generate a random, unique ID for a primary key on the fly and for example add it to the appropriate column.Thanks you in adavnce for any insights.

View 1 Replies View Related

Proc_problem Confusion

Nov 3, 2006

hi
i'm trying to write a stored_proc which involves 3 tables. Product, Orders and OrderDetails
i want to return products that haven't sold for a particular month. So these products aren't in the OrderDetails table. When i try and run this i get some really random results. when i change the Orders.OrderDate value to different months, i get the same result when i shouldnt. but then when i add the Orders.OrderDate row to the select statement, i get 400 results and i dont have that many products. the extended amounts are just for my datagrid
ALTER PROCEDURE proc_Report_NoSales_Septasset nocount onSELECT              Product.productID, Product.Title, Product.QtyOnHand, Product.Category, 0 as ExtendedAmount,              Product.BuyPrice, 0 as ExtendedAmount2 from       Orders, OrderDetails, Productwhere     Orders.OrderDate > '2006-09-01' and Orders.OrderDate < '2006-10-01'and        Orders.OrderID = OrderDetails.OrderID  and        Product.ProductID not in (Select distinct OrderDetails.ProductID from OrderDetails)/* Product.ProductID NOT EQUAL to OrderDetails.productID */and        Product.productID <> OrderDetails.productIDGROUP BY             Title, BuyPrice, Category, Product.ProductID, QtyOnHandORDER by            TitleRETURN
any ideas
cheers!!!

View 2 Replies View Related

Very Simple Confusion

Nov 7, 2006

hay friends
scene is that i wana read single multiple rows of a single column from a sql database and then want to shows those values in text box,,,
so plz tell me ho to do it.
 By using data set ,,data table or what to use for this and how....
wll be waiting for ur coordination

View 1 Replies View Related

Instance Confusion

Jul 26, 2007

I have built an application in web developer that has a sql data base in the App_Data
folder. It works locally fine.
I have uploaded it to my web host client successfully and it all displays. The web host is enabled
for asp.net.  But the sql data base is not working....no information is getting from the web application into
my instance of sql in App_Data.
My web host has given me an      e:/webs/mywebsitename.com/data/nameofdb.mdf
to alter in my web config connection string which I have done, but still not working.
I have also tried  every version of this in connection string without success
Im just confused about the e:  part................how is that going to get to my App_Data?
Can someone give me a quick overview of what the problem might be
richard

View 1 Replies View Related

DateTime Confusion.

Oct 25, 2007

Hi All,I'm stumped. I've got a stored procedure with a  number of input parameters, and working fine.I added two extra input parameters, FromDate datetime, ToDate datetime. I have not even included these in the SQL yet and just tried to execute the stored proc alone with manually inputting paramters, but I keep getting error: @FromDate: this input parameter cannot be converted.That surely means a formatting issue, so I copy and pasted a value directly from the database into this parameter field, and still get the same error. I've tried various formats, with single and double quotes, and without. But just dont know?And even when I populate these parameters in my code and call the stored proc, it returns no results either, even though I haven't included these new date paramters in the SQL select, so that means it was in error and no doubt a formatting issue on those date fields.format I used was:  17/07/2007 00:00:00 I tried to populate the parameters via the code as follows:            Dim dt As DateTime            DateFromTB1.Text = DateTime.Now            DateTime.TryParse(DateFromTB1.Text, dt)            SqlDataSource1.SelectParameters("FromDate").DefaultValue = dt I know there is an extra step for now (DateFromTB1.Text = DateTime.Now), this step will fall away and just parse the textbox.text field as entered by the user.Any help appreciated, thanks.  

View 4 Replies View Related

My ISNULL Confusion

May 1, 2008

I've got this query and it use to work or at least I thought it did. Could someone please help me with it.
Thank you
SELECT CID, CompletionDate, MarkedExport, CustomerName, EditUser, RouteID, WorkOrder FROM RouteCustomer WHERE (CompletionDate IS NOT NULL) AND (ExportDate IS NULL) AND (RouteID LIKE '%' + ISNULL(RouteID,@RouteID) + '%') AND (EditUser IS NULL OR EditUser = '' OR EditUser = @EmployeeID) AND (MONTH(CompletionDate) = ISNULL(MONTH(CompletionDate),@Month))
The problem comes with in the WHERE clause. What I wanted it to do is if the user did want to use a RouteID critera then the user would speified one else it wouldn't, and it was my belief that the ISNULL feature in SQL was the answer for that. same for the Month. I believe the EditUser is fine the way it is written.
thanks to anyone that can help me with this.
 Rex

View 9 Replies View Related







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